pivot address info to columns

D

DWS

I have 20,000 rows of address info that I would like to
parse into columns...data is in repeating format:

Name
Address
City, ST, Zip
Phone

Suggestions
 
D

DWS

Data is in four cells vertically in one column.

-----Original Message-----
Hi
is this kind of data in one cell or in 4 cells?

--
Regards
Frank Kabel
Frankfurt, Germany



.
Click to expand...
 
F

Frank Kabel

Hi
try the following formula in cell A1 on a separate sheet (assumption:
your source data is on sheet 1, starting in cell A1):
=OFFSET('sheet1'!$A$1,(ROW()-1)*4+COLUMN()-1,0)
and copy this formula 4 cells to the right and as far down as required.
After this select the new data range and copy it. Goto 'Edit - Paste
Special' and choose 'Values' to remove the formulas
 
D

DWS

Thank you for the help...works on the first row however
successive rows the formula places the data offset
further to the right in the successive rows then returns
at cell A6.

Appreciate your help and thoughts.
 
G

Guest

every fifth row is blank...I don't know if the formating
will hold in this email...if it does the data looks like
this:

Name Address Citystatezip phone
0 Name Address Citystatezip phone
0 0 Name Address

ect for four iterations then returns on the sixth row.

Gratefully!

Dave
 
F

Frank Kabel

Hi
then use the following formula:
=OFFSET('sheet1'!$A$1,(ROW()-1)*5+COLUMN()-1,0)
 
D

DWS

THANK YOU! worked perfectly...

-----Original Message-----
Hi
then use the following formula:
=OFFSET('sheet1'!$A$1,(ROW()-1)*5+COLUMN()-1,0)


--
Regards
Frank Kabel
Frankfurt, Germany



.
Click to expand...
 
Top