repeated transposition of multiple columns to a row

Hello, I have been using Excel to store XY coordinate data for various specimens. Each specimen has 16 XY coordinates (see attached sample data), and thus far I have been using a single "X" and "Y" column. However, for further analysis in another program, I need to export the data with a single row per specimen.

Currently, my data is in the format (total number of coordinates reduced for example):
Specimen1 1 2
Specimen1 3 4
Specimen1 5 6
Specimen2 1 2
Specimen2 3 4
Specimen2 5 6

And I want it in the format:
Specimen1 1 2 3 4 5 6
Specimen2 1 2 3 4 5 6

So I'm looking to shift over the xy data into xyxyxyxy(...) format, while deleting the extra specimen labels and blank space left behind between entries. I know very little of VBA programming--thus far I've been playing around with the TRANSPOSE function but with no luck. Any suggestions on how to do this?

Welcome to the forum, mcgrath.

Unless you particularly want a macro,

In E2 and down =INDEX(A:A, 16* ROW() - 30)

In F2, =INDEX($B:$C, 16 * ROW() - 31 + INT(COLUMNS($E2:F2)/2), MOD(COLUMNS($E2:F2), 2) + 1)

Then copy that across to AK2, then copy down.

Then you can replace all those formulas with values and delete columns A:D.

You're welcome. Would you please mark the thread as Solved? (How to ... in menu bar)