Combine multiple rows with same value into one plus more... in Excel

I have a list of product ids and order numbers. Each order number can have multiple product ids(since a person can buy more than one item). My goal is to combine each order number and product ids associated with it into one row. If there are more than one product id for each order then separate them my comma. Please see illustration below. I would like to avoid using manual approach a开发者_运维技巧s I have around 13000 rows. Please advise. Thanks.

Covert this:
order   product
158 866
161 960
163 976
163 884
164 1010
173 834
174 981
177 935
177 832
177 934

to this:
order   product
158 866
161 960
163 976,884
164 1010
173 834
174 981
177 935,832,934

The link given by @Nick provides a good non-VBA solution for where you want to keep the original data intact.

If you want to modify the data to leave only the merged rows, then a VBA solution will be required.

Here's a macro that will do the merging.

NOTES:

1. It assumes there is no other useful data on the sheet. If there is it will be deleted and or overwritten.

2. It works on the active sheet

3. It does the merging in memory variant arrays to provide resonable speed. Looping over the cells/rows would produce simpler code, but would run much slower

.

Sub MergeRows()
    Dim rng As Range
    Dim vSrc As Variant
    Dim vDst() As Variant
    Dim i As Long, j As Long

    ' Assumes data starts at cell A2 and extends down with no empty cells 
    Set rng = Range([A2], [A2].End(xlDown))

    ' Count unique values in column A
    j = Application.Evaluate("SUM(IF(FREQUENCY(" _
        & rng.Address & "," & rng.Address & ")>0,1))")
    ReDim vDst(1 To j, 1 To 2)
    j = 1

    ' Get original data into an array
    vSrc = rng.Resize(, 2)

    ' Create new array, one row for each unique value in column A
    vDst(1, 1) = vSrc(1, 1)
    vDst(1, 2) = "'" & vSrc(1, 2)
    For i = 2 To UBound(vSrc, 1)
        If vSrc(i - 1, 1) = vSrc(i, 1) Then
            vDst(j, 2) = vDst(j, 2) & "," & vSrc(i, 2)
        Else
            j = j + 1
            vDst(j, 1) = vSrc(i, 1)
            vDst(j, 2) = "'" & vSrc(i, 2)
        End If

    Next

    ' Remove old data
    rng.EntireRow.Delete

    ' Put new data in sheet
    Set rng = [A2].Resize(j, 2)
    rng = vDst

End Sub

If you want to keep the original data and summarize it somewhere else, you can use a method I detailed in a previous post. This would require you to make a list of all unique order numbers somewhere, and then use the CCARRAY UDF in the adjacent column. If the original data is on Sheet1, and the new summary list is on Sheet2, you would use the following formula to get the product information for the order number in A2 on Sheet2:

{=CCARRAY(IF(Sheet1!$A$1:$A$13000=$A2,Sheet1!$B$1:$B$13000),",")}

Just fill this formula down, and you're done.

To get the list of unique order numbers on Sheet2, just do something like copy the list of orders from Sheet1 to Sheet2, and then Remove Duplicates on Sheet2.