Excel: Group and combine row

[Jessica Blue]

Hi,

In excel, do we have a function or button that can transform the tables below?

Input table
Asset name 1 name 2 name 3 name 4
Toy. a.
Toy. B
Toy. C
Candy. D.
Candy. A
Play. C.




Output table
Asset name 1 name 2 name 3 name 4
Toy. a. B. C
Candy. D. A
Play c

Thanks

 

[alansidman]

Pivot Tables are what you need to learn. Your friend Mr. Google will instruct you on how to build them.

 

[Cubist]

Here's a formula option.

Book1
	A	B	C	D	E	F	G
1	Toy	a		Toy	a	B	C
2	Toy	B		Candy	D	A
3	Toy	C		Play	C
4	Candy	D
5	Candy	A
6	Play	C
Sheet2

Cell Formulas
Range	Formula
D1:D3	D1	=UNIQUE(A1:A6)
E1:G1,E3,E2:F2	E1	=TRANSPOSE(FILTER($B$1:$B$6,$A$1:$A$6=D1))
Dynamic array formulas.

 

[Jessica Blue]

Hi, thank you. Input table b and c was on column b and c.
Can I use the same formula?

 

[Jessica Blue]

Toy	a
Toy		B
Toy			C

Candy		A
Candy	d
Play	c

 

[Cubist]

No, the same formula won't work when your data are in different columns. Since you don't have Office 365, the formula's a little challenging. Are you open to VBA?

 

[Jessica Blue]

Yes. I open to VBA and asked my friend to build one. However, VBA can only accept process the data with around 16000row. And we got a overflow error since our data has 700k rows.

 

[Cubist]

Give this a try. I've made some comments where you might need to adjust the code.

Sub GroupData()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim lastRow As Long
    Dim key As Variant
   
    Set ws = ThisWorkbook.Worksheets("Sheet2") 'Change sheet name to suit
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'Assumes data starts in A

    Dim data As Variant
    data = ws.Range("A2:D" & lastRow).Value 'Assumes data starts in row 2
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    Dim i As Long
    For i = 1 To UBound(data, 1)
        key = data(i, 1)
        If Not dict.exists(key) Then
            dict.Add key, ""
        End If
        Dim j As Long
        For j = 2 To 4 ' 4 columns: Asset, Name1, Name2, Name3
            If data(i, j) <> "" Then
                If dict(key) = "" Then
                    dict(key) = data(i, j)
                Else
                    dict(key) = dict(key) & " " & data(i, j)
                End If
            End If
        Next j
    Next i

    Dim resultRow As Long
    resultRow = dict.Count
    Dim resultArray() As Variant
    ReDim resultArray(1 To resultRow, 1 To 4) ' 4 columns: Asset, Name1, Name2, Name3
    Dim rowIndex As Long
    rowIndex = 1

    For Each key In dict.Keys
        resultArray(rowIndex, 1) = key
        Dim values() As String
        values = Split(dict(key), " ")
        Dim colIndex As Long
        For colIndex = 0 To UBound(values)
            resultArray(rowIndex, colIndex + 2) = values(colIndex)
        Next colIndex
        rowIndex = rowIndex + 1
    Next key
   
    ' Output to F2
    ws.Range("F2").Resize(UBound(resultArray, 1), UBound(resultArray, 2)).Value = resultArray
End Sub