Why do you need blanks to go there, I would delete them
select the import column, press f5, select special and blanks,
press Ctrl - (or editdelete), select entire row, that will give you the
same data without the blanks, then use

=OFFSET(Sheet1!$A$1,(ROW(1:1)-1)*3+COLUMN(A:A)-1,0)

replace Sheet1 with the sheet name, if you insist of copying over empty
cells

=OFFSET(Sheet1!$A$1,(ROW(1:1)-1)*4+COLUMN(A:A)-1,0)

for the former copy across 3 cells and then copy down as long as needed
for the latter copy across 4 cells and then down

(use the fill handle to copy across/down)

--

Regards,

Peo Sjoblom



"PM" wrote in message
...
I have addresses in an Excel spreadsheet. Here is how they are formatted:

row 1: (blank)
row 2: name
row 3: address
row4: city, state, zip

This then repeats about 700 times.

Can anyone help me create a formula that copies these 4 rows to a single
row
in another tab? For example, row1 would go to columnA in the next tab,
row2
would go to columnB in the next tab, etc? I'd need it to repeat each
time,
so I have a tab that has each record in a single row (for printing
labels).

Please help! I cannot figure out the formula.

PM