Question: How to manipulate list block data into tabular format?

Insert a new row 1, and enter

B1 Name
C1 Hair
D1 Height

Get all your data from the first block next to the first entry of the first block using formulas:
In C2 enter
=B3
In D2 enter
=B4


Select C2:D5 (including next to the separating blank row), and copy it. Then select from C6 to D one row beyond your last block of data, and paste. If you don't get formulas next to your last block of data, you did not select the correct number of rows (it has to be a multiple of 4, the size of your block.)

Then copy C:D and paste values, sort based on C, delete column A, and you're done.

Assuming that you don't have Hair and Height but something else, and maybe more, just extend the technique to your block size.

This might help you out: The basic formula entered into D2 (leaving column C empty if you like) and copy down. Then adjust the column reference in columns E and F and copy down.

This assumes actual names to start in row 2.

Formula:

Please Login or Register  to view this content.

Size does not matter. It will take you longer to read this message than to actually perform the steps.

Insert a new row 1. Then in Cell B1, use the formula

=INDEX($A$2:$A$27,COLUMN(A1))

and copy across to cells C1:AA1.

In cells C2, use the formula
=OFFSET($B2,COLUMN(A1),0)
and copy across to cells D2:AA2.

Then copy D2:AA28, select D29:AA < row below your 580th data block> and paste. Then copy C:AA and paste values, sort based on column C, and delete column A. And you're done.

For a macro:

Please Login or Register  to view this content.