Transposing Data based on cell contents

Hi, really need some help on trying to figure this out. I have some data in multiple rows of varying length and I need to transpose this data from rows into two single columns as shown in the attached. The right hand column will contain all unique names and the left column may be repeated.

Many thanks

Capture.JPG

sorry my profile is out of date. Im using excel 2010.

I am sure that there is a better way for my second formula, but you can use:
In K4 and drag down. Array entered: Ctrl+Shift+Enter instead of regular Enter:

Please Login or Register  to view this content.

in J4 and drag down:

Please Login or Register  to view this content.

That's awesome, looks exactly what I wanted. Will play around with a bit it but many thanks!

Glad to help. Please mak the thread Solved from Thread Tools.

sorry just one more thing, how would this work if the data is on another sheet please? Simply substituting in references doesn't seem to work?

Hello, Im just revisiting this and realised that if you have any duplicate names it doesn't capture them? See attached Test4.xlsx

I've used two helper columns in Sheet1, G and H. This formula in G4:

=COUNTA(C4:F4)

just counts the number of entries that you have for each ID, and this one in H4:

=IF(G4="","",G4+H3)

just gives a cumulative sum of those names. Notice that I've put zero in H3. Both formula can be copied down to the bottom of your table.

Then in a separate sheet (After), with headings in row 1, I've used this formula in A2:

=IF(ROWS($1:1)>MAX(Sheet1!H:H),"",INDEX(Sheet1!B:B,MATCH(ROWS($1:1)-1,Sheet1!H:H)+1))

and this one in B2:

=IF(A2="","",INDEX(Sheet1!C:F,MATCH(ROWS($1:1)-1,Sheet1!H:H)+1,ROWS($1:1)-INDEX(Sheet1!H:H,MATCH(ROWS($1:1)-1,Sheet1!H:H))))

These can be copied down as far as you need to (I've copied to row 45 in the attached file).

Hope this helps.

Pete