Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Perpelexing Row to Column conversion
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Perpelexing Row to Column conversion
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) "Geno" wrote in message ups.com... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Perpelexing Row to Column conversion
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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Perpelexing Row to Column conversion
Bob Phillips wrote: 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) "Geno" wrote in message ups.com... 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. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column picked randomly with probability relative to number of entr | Excel Worksheet Functions | |||
how can i multiply two columns | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |