Excel xp question

[holly]

Ok i hope i can explain this clearly. I am trying to get all the informaiton
about a particular customer on one line/row. I have information in A/B/C/D
columns. In the B column i have the customers Street address in one line and
then the city/state/zip is in the line below that.
Example
1st row - name in column a / company in column b/ state in column C / Lic.#
in D
2nd row - nothing in column a / st. address in column b/ nothing in column
C/ dito
3rd row - nothing in column a / city,state,zip in colum b/ nothing in column
C/ dito

*So you see in column b is everything i need to be in order in the 1st row
to be able to make labels. I have no need for 2nd/3rd row but the source i
imported it from put it into this format.
Thanks and I hope there is a solution to my problem
Holly

 

[Otto Moehrbach]

Holly
Are you saying that you want to delete the second and third rows, for
every name entry? Or are you saying you want to incorporate the data in the
second and third rows into the first row, then delete the second and third
rows, for every name entry?
Is this pattern, that you describe, the same for all name entries? That
is, do they all have both a second and a third row? Otto

 

[holly]

Yes, i want to be able to incorporate the data in the second and third row
into the first row but i do not know how. I have about 5000 names and all of
them have the address and the city on different rows.
Thanks,
Holly

 

[Otto Moehrbach]

Holly
Let's get down to the details.
First, do you want the second and third rows deleted once their data is
incorporated into the first row? I would think so.
You say you have the company name in Column B and the address below that (in
the same column) in 2 rows.
Do you want the data in the current Columns C & D of the first row shifted
to the right 2 columns and the address placed in the 2 then blank columns C
& D of that first row? I think this is what you want.
I will assume row 1 is a header row and your first line of data starts in
row 2. Is that correct?
Otto

 

[Otto Moehrbach]

In your first post you said that the State is in Column C and that the City,
State, and Zip were in the second row down in Column B. That's 2 states.
Is that right? Otto

 

[Otto Moehrbach]

Holly
Try this macro. I assumed you wanted the 2 rows deleted and Columns C &
D moved over. Make a copy of your file and run this macro in the copy file
to make sure it does what you want before you trust it with your real file.
Post back if you need more. HTH Otto
Sub ShuffleData()
Dim RngColA
Dim c As Long
Application.ScreenUpdating = False
Columns("C").Insert Shift:=xlToRight
Range("C1").Value = "Street"
Range("D1").Value = "City, State, Zip"
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For c = RngColA.Count To 1 Step -1
If Not IsEmpty(RngColA(c).Value) Then
RngColA(c).Offset(1, 1).Resize(2).Copy
RngColA(c).Offset(, 2).PasteSpecial Transpose:=True
RngColA(c).Offset(1).Resize(2).EntireRow.Delete
End If
Next c
Application.ScreenUpdating = True
End Sub