Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    2

    Unanswered: VLOOKUP and IF Help

    Hi there,

    I wonder if anyone can help me, I have a number of rows of data for the same person and am wondering how I can put the different rows all on one row, example below: -

    WHAT I HAVE: -

    Name - Child - Child's Name
    John - Child 1 - Jack
    John - Child 2 - Jill
    John - Child 3 - Jane

    WHAT I NEED: -
    Name - Child 1 - Child 2 - Child 3
    John - Jack - Jill - Jane

    Any help would be greatly appreciated.

    Thanks
    Stuart

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy, and welcome to the board.

    Here is one way to do it. I added a fourth column (D on the attached), named it NameLU and put into cell D2 this formula:

    =A2&B2

    Then I used five named ranges:

    NameList
    =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

    ChildList
    =OFFSET(NameList,0,1)

    NameList
    =OFFSET(NameList,0,2)

    NameLU
    =OFFSET(NameList,0,3)

    NameArray
    =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,3)

    With the new names in Column F, and Child 1 in G, etc., I put John in F2, and in G2 this formula:

    =INDEX(NameArray,MATCH($F2&G$1,NameLU,FALSE),3)

    Copy across and down as needed. This allows you to add as many names in columns A:C, and still work.

    HTH
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Nov 2007
    Posts
    2
    Thanks very much for that, I didn't expect a response as fast as that!

    It works perfectly, but do you know how the blank rows can automatically be deleted?

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Guess I am not sure what you mean by blank rows. If you are going to be adjusting the old data, then it would be better to put the lookup formulas on another worksheet. That way if you have blank rows in your original data you can delete without interferring with the lookups.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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