Transpose multiple?

[jackmcguigan1]

Hi all,

I'm hoping someone can help. I've been given a spreadsheet that has just over 2000 rows. From the attached image you'll get an idea (actual data has obviously been changed). Each record has 6 rows with the field name showing. I nneed to have them in the correct columns but obviously that's not a straight transpose. I'm thinking an IF statement saying if cell = Name, cell to right is moved to next blank cell in a column I choose. If I did that as a nested IF for all fields I'd evenntually get the right columns.

Unless annyone can suggest a simpler route.

Any help very much appreciated!

Data_Transpose.png

[oeldere]

HOW TO ATTACH YOUR SAMPLE WORKBOOK:

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[jackmcguigan1]

I found the answer to my own question. If I use the formula:

=IF(COLUMNS($A:A)>6,"",INDEX($A:$A,COLUMNS($A:A)+6*(ROWS($1:1)-1),1))

then copy it across the columns it pulls the correct data for me!