NEVERMIND! I figured it out.

In case anyone is interested, and working with a different number of rows to
transpose, the variable is in this formula that he gave:
=IF(A2="",100*INT(A1/100)+101,IF(TRIM(B3)="","",
IF(TRIM(B4)="",100*INT(A2/100)+4,A2+1)))

I changed the +4 to a +7 and it made it work for a list of 7 rows. I
presume that means you could make that any number that equals the maximum
number of rows you are dealing with.

"thmarine" wrote:

Can you please tell me how to make this work for a similar situation. What
you have shown works for me, except I have exactly seven (7) rows followed by
a space for each entry, as follows:
A1=Name
A2=Street
A3=City, State, Zip
A4=phone
A5=fax
A6=e-mail address
A7=web site
A8=<blank row
then pattern repeats.

I want those to be transposed to colums from rows. Just like the original
poster.

I used your formula and it worked fine for my first six(6) rows, but my 7th
row did not go into my 7th column, it is blank. Is there something about the
formula that needs changing to work for 7 instead of 4 rows?

Thanks!!!


"MyVeryOwnSelf" wrote:

I have a list of addresses that are all in one column but they are not
equal in lenght, some have 3 lines and others have 4 lines, there is
no row separating each record. I would like to use this data for
mailing list. So I'd like to see the information as follows:
Name Address 1 Address 2 City/St/Zip

I know how to break up the City/ST/Zip data.

Transpose works but I have to do each group separately and it's taking
me forever.

Maybe something like this would help.

Put the list in column B of Sheet1.

In Sheet1!A1 put
1

In Sheet1!A2 put
2

In Sheet1!A3 put
=IF(A2="",100*INT(A1/100)+101,IF(TRIM(B3)="","",
IF(TRIM(B4)="",100*INT(A2/100)+4,A2+1)))
and extend down to the end of the list.

Next go to Sheet2, and put in Sheet2!A1
=IF(ISNA(
VLOOKUP(COLUMN()+100*(ROW()-1),Sheet1!$A:$B,2,FALSE)),"",
VLOOKUP(COLUMN()+100*(ROW()-1),Sheet1!$A:$B,2,FALSE))
Extend A1 to A4, then extend A1:A4 down as far as needed.

The City/ST/Zip should end up in column 4 of Sheet2, with the other address
parts in columns A:B (for 3-line addresses) or A:C (for 4-line addresses).