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

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

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?

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.