Transpose problem 1

[z2gary]

Hello:
The following code was written by Geoff and I copied it to a module in my Excel 2000.
It works perfect.
I need help with modifing it to work with my scenerio.
I need the user to select the range in column "A" because the addresses are not consistant.
Most are 3 rows long without blank rows.
Others are 2 rows and still others are 4 rows.
I'm able to get the range selection with the input box function.
However, I don't know how to modify this to accept the selection and continue on the next row in sheet 2.

Sub TransposeAddresses()
Dim bRow As Long, sht As Worksheet, y As Long
Set sht = Sheets("Sheet2")
bRow = Range("A" & Rows.Count).End(xlUp).Row
y = 2
For I = 1 To bRow Step 3
For x = 0 To 2
sht.Cells(y, x + 1).Value = Cells(I + x, 1).Text
Next x
y = y + 1
Next I
End Sub
Thank you
z2Gary

 

[SkipVought]

Hi,

How about posting some examples of the data you're working with and an example of what you want the result to look like.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[z2gary]

Skip here is the data.
The program transposes these addresses from column "A" sheet 1 to A B C on sheet 2.

Mr. David Lebeau
3l71 Laubert Road
Atwater, OH 44201-9792
Christine & Thomas Knippenberg
Hiram, OH 44234
Tina Creedon
P.O. Box 2012
196 Critchlow Sch. Road
Renfrew, PA 16053

There are hundreds of records. Most are 3 addresses long. However some are 2 and some 4 so the user needs to select.
When they select a new set of data it needs to continue on the next available row in sheet 2. They are instructed not to exit until they are done.

 

[SkipVought]

Data conversion, data mapping, data cleanup is a time consuming and tedious job.

You can use logic, if you can decsribe the logic and if the logic fits for every single instance.

You mention mapping the data to columns A, B & C, but some of your source data maps to 2 or 4 columns. What would the logic be for those instances.

Can you say, in EVERY INSTANCE, that the LAST ROW of an address is structured...
[tt]
Some string, SS ZZZZZ

or

Some string, SS ZZZZZ-ZZZZ
[/tt]
where SS is a state abbreviation and ZZZZZ or ZZZZZ-ZZZZ is a numeric zip code?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[z2gary]

Skip:
Your right.
Sometimes can't see the forest for the trees.
Thanks for the response anyway.
z2Gary

 

[SkipVought]

Here are two spreadsheet fommulas you can use to categorize and identify your data.

Assumes that your address data is in column A beginning in Row 2...
[tt]
B2: =IF(ISERR(FIND(",",$A1)),B1+1,1)
C2: =IF(ISERR(FIND(",",$A1)),C1,C1+1)
[/tt]
The formula is column B, numbers each line of the address.
The formula in column C, groups all the lines of a common address, by a number.

So with your example, my results are...
[tt]
A B C
Name Address City Stare Zip
Mr. David Lebeau 1 0
3l71 Laubert Road 2 0
Atwater, OH 44201-9792 3 0
Christine & Thomas Knippenberg 1 1
Hiram, OH 44234 2 1
Tina Creedon 1 2
P.O. Box 2012 2 2
196 Critchlow Sch. Road 3 2
Renfrew, PA 16053 4 2
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, my logic assumes that the [COMMA] character ONLY appears in City State Zip rows and NO WHERE ELSE.

If that assumption is not true, then some other logic must be used.

Also, using formulas like this, you can SORT all the 2 per, 3 per and 4 per addresses into groups using the COUNTIF function to categorize. Then apply the transform logic that fits each group.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[z2gary]

Skip:
That is an ingenious formula.
Thank you so much.
Sometimes you can think for hours and just get lost.
Now I don't have to have the user select anything.
Have 2 stars.
z2Gary

 

[ThichTraXanh]

it seems to me that all your addresses have coma in the last row. Why don't you take the coma as a key to separate each address?

Mr. David Lebeau
3l71 Laubert Road
Atwater, OH 44201-9792 <--Last row of this address
Christine & Thomas Knippenberg
Hiram, OH 44234 <--Last row of this
Tina Creedon
P.O. Box 2012
196 Critchlow Sch. Road
Renfrew, PA 16053 <-last row of this

do while to the last row
break address from first coma, next coma
do you thing
loop

 

[SkipVought]

ThichTraXanh,

You appear to not have read all the postings in this thread.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]