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.

Thanks for your help.

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).

Sorry- I just check my post b/c I've been gone for a few days. I tried this
but it doesn't work - I get a syntax error from the first formula. It tells
me that
=IF(A2="",100*INT(A1/100)+101,
IF(TRIM(B3)="","",IF(TRIM(B4)="",100*INT(A2/100)+4,A2+1))) is invalid when I
click on the Fx help.

"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).

Your formula is not invalid.
Do you have strange unprintable characters (perhaps at the line breaks) in
your version?
--
David Biddulph

"LizE" wrote in message
...
Sorry- I just check my post b/c I've been gone for a few days. I tried
this
but it doesn't work - I get a syntax error from the first formula. It
tells
me that
=IF(A2="",100*INT(A1/100)+101,
IF(TRIM(B3)="","",IF(TRIM(B4)="",100*INT(A2/100)+4,A2+1))) is invalid when
I
click on the Fx help.

"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).

This is what I have
=IF(A2="",100*INT(A1/100)+101,IF(TRIM(B3)="","",IF(TRIM(B4)="",100*INT( A2/100)+4,A2+1)))

"David Biddulph" wrote:

Your formula is not invalid.
Do you have strange unprintable characters (perhaps at the line breaks) in
your version?
--
David Biddulph

"LizE" wrote in message
...
Sorry- I just check my post b/c I've been gone for a few days. I tried
this
but it doesn't work - I get a syntax error from the first formula. It
tells
me that
=IF(A2="",100*INT(A1/100)+101,
IF(TRIM(B3)="","",IF(TRIM(B4)="",100*INT(A2/100)+4,A2+1))) is invalid when
I
click on the Fx help.

"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).

No syntax error for me from that formula.

You don't, by any chance, have your machine set up (Windows/ Regional
Options) to use a semi-colon, rather than a comma, as a list separator, do
you?
--
David Biddulph

"LizE" wrote in message
...
This is what I have
=IF(A2="",100*INT(A1/100)+101,IF(TRIM(B3)="","",IF(TRIM(B4)="",100*INT( A2/100)+4,A2+1)))

"David Biddulph" wrote:

Your formula is not invalid.
Do you have strange unprintable characters (perhaps at the line breaks)
in
your version?
--
David Biddulph

"LizE" wrote in message
...
Sorry- I just check my post b/c I've been gone for a few days. I tried
this
but it doesn't work - I get a syntax error from the first formula. It
tells
me that
=IF(A2="",100*INT(A1/100)+101,
IF(TRIM(B3)="","",IF(TRIM(B4)="",100*INT(A2/100)+4,A2+1))) is invalid
when
I
click on the Fx help.

"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).

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).

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).