excel - Transpose matrix into 2 columns

Question

Hi I have an excel table which is formatted like this:

 NR   | 1 |2    |3   |4   |5
 Name |tom|alice|jack|brad|ed

 NR   | 6 |7   |8  |9    |10
 Name |ted|ally|jon|bronn|cony

 NR   | 11  |12  |13    |14    |15
 Name |trish|anna|thomas|justin|dick

 NR ....
 Name ...

I have 2000 rows like this. Obviously this table structure is really bad and unusable for any data analysis or data extraction. So I am trying to format it like this:

 Nr | Name
 _________
 1  | tom
 2  | alice
 3  | jack
 4  | brad
 5  | ed
 6  | ted
 7  | ally
 8  | jon
 9  | bronn
 10 | cony
 11 | trish
 12 | anna
 13 | thomas
 14 | justin
 15 | dick
 ...| ...

I have been manually transposing and inserting each two rows but that is taking way too long. Can I do this more efficiently or automatically? I am not really that good with advanced excel or VBA. Can I do this using only formulas?

Answer 1

Assuming your data start in A1, put this into F1:F10000:

=INDEX($A$1:$E$4000,2*(INT((ROW()-1)/5)+1),MOD(ROW()-1,5)+1)