Multiple rows converted to one row

[Guest]

Imported table from DOS database to Excel 2003. Have a worksheet containing
records of 6 columns and 3 rows per record with each record separated by a
blank row. Each worksheet contains 4,000-7,000 such records. I need to
transpose each record into a single row for sorting purposes. Have tried the
Transpose function, but can use that function only on 1 record and 1 column
at the time. Iis there a function/formula to transpose that can be repeated
through row 7,000 that will apply to 3 rows and 6 columns, then skip a row,
then next 3 rows, etc. Can anyone offer suggestion? Thank You!

 

[Rob van Gelder]

I'm guessing it's a once-off data migration?

I would use a program like TextPad for massaging the data before Excel
import... The macro recorder is very nice.
That's just me.

Excel:
Option 1
Put your cursor to top left corner of a record block then start the macro
recorder
Be sure to set a shortcutkey and click the "Relative Reference" button.
Perform your actions then leave your cursor in the top left of the next
record block.
Run the macro over and over.

Option 2

G1: =A2
H1: =B2
....
L1: =F2
M1: =A3
N1: =B3
....
R1: =F3
Highlight G1:R4
Copy
Highlight G1:R7000
Paste

Highlight G1:R7000
Copy
PasteSpecial - Values
Sort by row G
Scroll Down
Delete all the rows which have a blank row G but non-blank row A

 

[Rob van Gelder]

Correction:
Where I wrote Sort by column G, it should have said Highlight the entire
sheet then Sort by column G

 

[Ken Wright]

First get rid of the blank rows, Select any column, Edit / Go to / Special /
Blanks, then Edit / Delete / Entire row

Now assuming your data on sheet1 starting A1 with no headers, in cell a1 on
sheet 2 put the following and copy to A1:R2000 which should cover it

=OFFSET(Sheet1!$A$1,((ROW()-1)*3)+(FLOOR(COLUMN()-1,6)/6),(COLUMN()-1)-(FLOO
R(COLUMN()-1,6)))

Copy whole dataset and paste special as values.