Convert repeating data into a list

Hello,
I am having real trouble converting repeating data into a list format. The first table below shows how my data is right now. Basically, I would like to convert the repeating data elements (name, age, gender and grade) into a list, as you will see in the second table. I've tried a bunch of things, but I'm stuck.

Is there a way to convert my data into a list? Any help would be very much appreciated. I have attached an Excel file to more clearly show things.

How the Data is formatted right now (I show "|" to illustrate the separation of columns in Excel. And each row in my data shown here is in a separate row in Excel).
Name | Peter
Age | 26
Gender | M
Grade | A
Name | Alexia
Age | 23
Gender | F
Grade | C
Name | Scott
Age | 32
Gender | M
Grade | B


And I would like the data to look like this:
Name | Age | Gender | Grade
Peter | 26 | M | A
Alexia | 23 | F | C
Scott | 32 | M | B


Thanks,
Peter (in Seattle)

Yes you can do this (using INDEX formulae or VBA) but...

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Based on your file, firstly:

Please Login or Register  to view this content.

Then

Please Login or Register  to view this content.

Originally Posted by Peter33

Hello,
I am having real trouble converting repeating data into a list format. The first table below shows how my data is right now. Basically, I would like to convert the repeating data elements (name, age, gender and grade) into a list, as you will see in the second table. I've tried a bunch of things, but I'm stuck.

Is there a way to convert my data into a list? Any help would be very much appreciated. I have attached an Excel file to more clearly show things.

How the Data is formatted right now (I show "|" to illustrate the separation of columns in Excel. And each row in my data shown here is in a separate row in Excel).
Name | Peter
Age | 26
Gender | M
Grade | A
Name | Alexia
Age | 23
Gender | F
Grade | C
Name | Scott
Age | 32
Gender | M
Grade | B


And I would like the data to look like this:
Name | Age | Gender | Grade
Peter | 26 | M | A
Alexia | 23 | F | C
Scott | 32 | M | B


Thanks,
Peter (in Seattle)

You can find your file below.

.

Worth adding that INDIRECT (unlike INDEX) is Volatile so pending volume of data this may be worthy of consideration... for more info. on Volatility see link in sig. to Charles Williams' site.

dk's can be put into one formula if you dont want an added cell
=IF(ROWS(D$2:D2)>COUNTA($A:$A)/4,"",INDEX($B:$B,COLUMNS($D2:D2)+(4*(ROWS(D$2:D2)-1)))