Geek asks and answers

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

I have a list of product identifiers and order numbers. Each order number can have several product identifiers (since a person can buy more than one item). My goal is to combine each order number and product identifiers associated with it in one line. If there is more than one product identifier for each order, separate them with a comma. See the figure below. I would like to avoid using a manual approach, since I have about 13,000 lines. Please advise. Thank you

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
+3
source share
2

, @Nick, , VBA, .

, , VBA.

, .

:

  • , . , .

  • . / ,

.

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
+1

, , . , CCARRAY UDF . Sheet1, - Sheet2, , A2 Sheet2:

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

, .

Sheet2, - Sheet1 Sheet2, Remove Duplicates Sheet2.

0

More articles:


All Articles