# Programming help

#### kienedie

##### New member
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
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