How do I combine column values in a specific cell

Question

I have this table;

Table

I'd like to be able to list these Options with Options' Values in a single cell for every given Product ID like this one: Image

Also keep in mind that I have 1800 Product ID's listed in that table so manually extracting the data is pretty hard.

Thank you.

Answer 1

This UDF will give you your desired output for a certain option for a certain product ID:

Function UDF_ListValues(prodId As Variant, attrib As String, R As Range)

    Dim colValues As Collection
    Dim curRow As Range
    Dim found As Boolean
    Dim value As Variant
    Dim first As Boolean

    If R.Columns.Count <> 3 Then
        UDF_ListValues = xlErrNA
    Else
        Set colValues = New Collection
        For Each curRow In R.Rows
            If curRow.Cells(1, 1).value = prodId And curRow.Cells(1, 2).value = attrib Then
                found = False
                For Each value In colValues
                    If curRow.Cells(1, 3).value = value Then
                        found = True
                        Exit For
                    End If
                Next
                If Not found Then colValues.Add curRow.Cells(1, 3)
            End If
        Next

        first = True
        For Each value In colValues
            If first Then
                UDF_ListValues = attrib & ":"
                first = False
            Else
                UDF_ListValues = UDF_ListValues & "¦"
            End If

            UDF_ListValues = UDF_ListValues & value
        Next

        Set colValues = Nothing
    End If

End Function

To get the exact output like in your example, you can call this function in cell F2 as follows:

= UDF_ListValues( E2, "Size", $A$2:$C$22 ) & ";" & UDF_ListValues( E2, "Colour", $A$2:$C$22 )

or you could easily modify the code to automatically list all occurring options.

Answer 2

Please try this method.

Sub Macro()
Dim lngRow As Long
For lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If StrComp(Range("A" & lngRow), Range("A" & lngRow - 1), vbTextCompare) = 0 Then
If Range("B" & lngRow) <> "" Then
Range("B" & lngRow - 1) = Range("B" & lngRow - 1) & "|" & Range("B" & lngRow)
End If
Rows(lngRow).Delete
End If
Next
End Sub

Answer 3

If you have the latest update in Office 365 or are using the online app you could use the following array formula:

="Size:" & TEXTJOIN("|",TRUE,IF(($A$2:$A$12=E2)*($B$2:$B$12="Size"),$C$2:$C$12,"")) & ";Colour:" & TEXTJOIN("|",TRUE,IF(($A$2:$A$12=E2)*($B$2:$B$12="Colour"),$C$2:$C$12,""))

Being an array formula it must be confirmed with Ctrl-Shift-Enter when exiting edit mode instead of Enter or Tab. If done properly Excel will put the {} around the formula.