Perpelexing Row to Column conversion

[Geno]

I have a spreadsheet that has a list of companies and contacts within
those companies. Some rows have one company with one contact for that
company. Many rows have the same company info duplicated with a
different contact info for that company. What I need is unique rows
with unique company name and all the contacts for that company in the
same row.

 

[Bob Phillips]

VBA?

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
Cells(i, "B").Resize(, 253).Copy Cells(i - 1, "C")
Rows(i).Delete
End If
Next i

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

not sure if this is a one time cleanup kind of thing or how much data you but
one way you could do it is to autofilter, then select a company. It will
show all the entries and contacts for that company. You could then copy all
of the contacts below the first row and then 'paste special'....'transpose'
to the right of your 1st contact in the first row. This will give you a row
with the company name and the contacts in cells to the right of the company
name. You would then have to delete all extra rows for that company and move
onto filtering the next company. If you have tons of companies and tons of
data this would probably not be the optimum way to do it but if you don't
have tons of data it will give you what you want.

 

[Geno]

VBA?

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
Cells(i, "B").Resize(, 253).Copy Cells(i - 1, "C")
Rows(i).Delete
End If
Next i

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Great!!! It works, almost!!. Suppose there are 11 columns of info
associated with each contact. In your script, assume A2 is the company
name that duplicates on several rows and I need contact info in columns
B2 thru B12 that is different on those each of those several rows. I
realize that the result will be a very wide spreadsheet but that is
what I need.