Results 1 to 2 of 2

Thread: Programming help

  1. #1

    Programming help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    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.
    Attached Files Attached Files


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •