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
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.
Bookmarks