Programming help

kienedie

New member
Joined
May 7, 2013
Messages
1
Reaction score
0
Points
0
Hello,

This is my first post.

Attached are two spreadsheets. One contains a list of records i.e. name, company name, title, address etc. The second shows how i would like to see these records (in a row format as opposed to a column format). Is there a way to program this? I have about 450-500 records in a column format.

Can anybody help me?

Thank you,
Kevin
 

Attachments

  • list of records.xlsx
    14.2 KB · Views: 19
  • requested layout.xlsx
    10.3 KB · Views: 11

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Since there is a inconsistency of number of records per individual (i.e. some have email address and some don't), we need to find a way to identify the names.

My suggestion is this.

First insert a blank row above the data, then in B2 enter this formula:

=IF(LEFT(A7)="(",COUNT(B$1:B1)+1,"")

copied down.

This checks if there is a phone number 5 cells below which seems to be consistent (identified with a open bracket at the left side). If so, then enter a cumulative number identifying the row as containing a person's name.

Then in Sheet2, A2 enter formula to get the names:

=IFERROR(INDEX('Table 1'!A:A,MATCH(ROWS($A$2:$A2),'Table 1'!$B:$B,0)),"")

copied down as far as needed.

Then in B2 to get remaining info:

=IF($A2="","",INDEX('Table 1'!$A:$A,MATCH($A2,'Table 1'!$A:$A,0)+COLUMNS($B$1:B$1)))

copied down same distance, but copied across only to columm F.

Because of the inconsistency in email address availability the formula in G2 is amended a bit to:

=IF($A2="","",IF(ISNUMBER(FIND("@",INDEX('Table 1'!$A:$A,MATCH($A2,'Table 1'!$A:$A,0)+COLUMNS($B$1:G$1)))),INDEX('Table 1'!$A:$A,MATCH($A2,'Table 1'!$A:$A,0)+COLUMNS($B$1:G$1)),""))

copied down the full distance.
 

Attachments

  • Copy of list of records.xlsx-1.xlsx
    20.4 KB · Views: 9
Top