Delete Duplicates & Concatenate Unique Data

[daysable]

Hi, I am tearing my hair out on this one.
I'm not very familiar with macros or complicated formulas in excel. I can run a v-look up or a basic sum formula, but that is about it and I need help with cleaning up a spreadsheet.

I have 4 columns:
Turn-In
Product #
Product Name
Color

Turn-In, Product #, and Product Name could all have the same data in the cells, but Color column has different data. So I could have 2 or 3 rows of the same data and the only difference is the color cells. How can I filter my spreadsheet so there is only 1 row for each product # but have the color names concatenate in a new column?

Any help would be great. Thanks.

-Kristin

I pasted example data below, hopefully it comes out ok once posted:

<table border="0" cellpadding="0" cellspacing="0" width="470"><col style="width: 48pt;" width="64"> <col style="width: 67pt;" width="89"> <col style="width: 132pt;" width="176"> <col style="width: 106pt;" width="141"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" width="64" height="17">Turn-In</td> <td class="xl24" style="border-left: medium none; width: 67pt;" width="89">Product #</td> <td class="xl24" style="border-left: medium none; width: 132pt;" width="176">Product Name</td> <td class="xl24" style="border-left: medium none; width: 106pt;" width="141">Color</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" align="right" height="17">Jun-11</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">33452</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Basic V-Neck Tank</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Black-1X</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" align="right" height="17">Jun-11</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">33452</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Basic V-Neck Tank</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Red-RU</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" align="right" height="17">Jun-11</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">85948</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Denim Relaxed Capris</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Stonewash-MA</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" align="right" height="17">Jun-11</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">39853</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Plunge Bra</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">White-1A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" align="right" height="17">Jun-11</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">39853</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Plunge Bra</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Black-1X</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" align="right" height="17">Jun-11</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">93849</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Triple Layer Necklace</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Raspberry-MR</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" align="right" height="17">Jun-11</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" align="right">85745</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Gold-Tone Ring</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Gold-Tone-GT</td> </tr> </tbody></table>

 

[Domenic]

Assuming that the data starts at A1, try...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] Rng [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] LastColumn [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]

    Columns("D").Insert
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    
    [color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] LastRow
        Cells(i, "D").Value = Cells(i, "A").Value & "#" & Cells(i, "B").Value
    [color=darkblue]Next[/color] i
    
    [color=darkblue]Set[/color] Rng = Range(Cells(1, 1), Cells(LastRow, LastColumn))
    
    [color=darkblue]With[/color] Rng
        .Sort key1:=Range("A1"), order1:=xlAscending, key2:=Range("B1"), order2:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTop[color=darkblue]To[/color]Bottom
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=darkblue]For[/color] i = LastRow To 3 [color=darkblue]Step[/color] -1
        [color=darkblue]If[/color] Cells(i, "D").Value = Cells(i - 1, "D").Value [color=darkblue]Then[/color]
            Cells(i - 1, "E").Value = Cells(i - 1, "E").Value & ", " & Cells(i, "E").Value
            Rows(i).Delete
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    
    Columns("D").Delete
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    MsgBox "Completed...", vbInformation
        
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[daysable]

This works perfectly! Thank you so much!

 

[Domenic]

You're very welcome! Thanks for the feedback!