Data formatting question

[Jim Short]

Hello,

I tried searching for this in Google groups but did not find anything
that would do what I needed.

I have inherited an Excel file that looks like it was originally a .csv.
There are over 8000 rows of data. The problem is that it looks like
every entry got split between two lines. In other words, row 1 and row 2
should all be on row 1, and similarly for rows 3-4, 5-6, etc.

Both the even and the odd rows have data in cells A through G, except
for some blanks. Essentially I want to cut every even row and paste it
to the row above it, starting at column H. Graphically, I want
A1 B1 C1 D1 E1 F1 G1
A2 B2 C2 D2 E2 F2 G2
to become
A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1 N1 O1
and similarly for pairs 3-4, etc.

I suppose this will take a macro but I can't figure out how to do it.

Any help?

Jim

 

[Frank Kabel]

Hi Jim
a non macro solution:
On a separate sheet (lets say sheet 2) enter the following in cell A1
=OFFSET('sheet1'!$A$1,(ROW()-1)*2+INT(COLUMN()/8),MOD((COLUMN()-1);7))
copy this formula to the right and down

After this select the resulting range, copy it and insert it again as
Values (goto 'Edit - Paste Special') to delete the formulas

 

[Jim Short]

Hello Frank,

The OFFSET statement is exactly what I needed. Thanks for the help.

Jim