Combining Like Items Across Multiple Columns/Rows?

**softserve** · 06-09-2009, 06:33 PM

I assume this needs a macro... I was hoping someone here could help me. This isn't absolutely necessary, but being able to do this easily would cut down on a lot of tedious jobs for me.

Let's say I have a set of data like this (I tried to space it out as best I could to give the idea):

Site                     Type A       Type B      Type C
www.google.com          12               10       34
www.google.com                         48          
www.msn.com             5       
www.yahoo.com                        165
www.thing.com             8   
www.yahoo.com          3                          34

Basically, I want to combine the redundant information. So if Google.com has results in each type, I want them to appear on the same row. But at the same time, I don't care that they have 2 Queens, I just care that they have a Queen at all.

The result I'm looking for would be like this..

Site                    Type A       Type B        Type C
www.google.com              12          10, 48           34
www.msn.com                 5
www.yahoo.com              3              165            34
www.thing.com                8

I don't want items from the same type and site to be added together, but rather just put in the same cell next to one another..

I am specifically working with URLs in the site column, however, most of which have content following the .com that I need to keep (like google.com/language_tools_, google.com/article, etc. - they'd not be stripped down to google.com, but be considered separate entities).

Hopefully that makes sense. I just don't even know what to try and searching hasn't helped so far.

I use Excel 2007 right now... Thanks for any help!

**JBeaucaire** · 06-09-2009, 09:02 PM

Hi softserve, welcome to the forum.

Is it OK to SORT the list first so that like items are next to each other in the listing?

Any change you'll post up a good sample REAL workbook? Click on GO ADVANCED and use the paperclip icon to upload your sample book. That would make this much simpler.

**rylo** · 06-09-2009, 09:16 PM

Hi

This was written for 2003, but see how it goes.

Sub aaa()
  Range("A:D").Sort key1:=Range("A1"), order1:=xlAscending, header:=xlYes
  For i = Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
    If Cells(i - 1, 1) = Cells(i, 1) Then
      For j = 2 To 4
        If Not IsEmpty(Cells(i, j)) Then
          If IsEmpty(Cells(i - 1, j)) Then
            Cells(i - 1, j).Value = Cells(i, j).Value
          Else
            Cells(i - 1, j).Value = Cells(i - 1, j).Value & "," & Cells(i, j).Value
          End If
        End If
      Next j
      Cells(i, 1).EntireRow.Delete
    End If
  Next i
End Sub

rylo