Transpose Onto Sheet 2 Please.

[Dazzawm]

I have sheet 1 with 2 columns, a number in A and a transmission in column B. I need the number and the transmission transposed onto sheet 2. There may be a unique number in A or several the same with different transmissions in B. In the example I have highlighted the duplicates in A. The yellow one has the same number in A but 3 transmissions in B so they will go under the transmission header in B (also yellow), the same with blue, green and orange.

Sheet A will consist of several hundred thousand rows with more permutations so this is just a small example. It would be helpful if the headers got created on sheet 2 please. Thanks.

The combination of headers will just include -

Direct Drive
CVT
Manual
Semi-Automatic
Automatic

Code	Transmission
M10105420000001	CVT
M10105420000002	CVT
M10105420000003	CVT
M10105420000003	Direct Drive
M10105420000004	CVT
M10200400000008	Manual
M10200400000008	Semi-Automatic
M10200400000008	CVT
M10200400000009	Manual
M10200400000009	Semi-Automatic
M10200400000010	Manual
M10200400000011	Manual
M10200400000012	Manual
M10200400000012	Automatic
M10200400000013	Direct Drive

After

CVT	Manual & Semi-Automatic & CVT	Manual & Semi-Automatic	Manual	Manual & Automatic	CVT & Direct Drive	Direct Drive
M10105420000001	M10200400000008	M10200400000009	M10200400000010	M10200400000012	M10105420000003	M10200400000013
M10105420000002			M10200400000011
M10105420000004

 

[hagia_sofia]

Try this (replace A2:A16 with your actual Code range and B2:B16 by your actual Transmission range):

=LET(x,A2:A16,y,B2:B16,code,UNIQUE(x),array,HSTACK(BYROW(code,LAMBDA(a,TEXTJOIN(" & ",TRUE,FILTER(y,x=a)))),code),headers,UNIQUE(HSTACK(CHOOSECOLS(array,1))),TRANSPOSE(IFERROR(HSTACK(headers,DROP(REDUCE("",headers,LAMBDA(a,b,VSTACK(a,TRANSPOSE(FILTER(CHOOSECOLS(array,2),CHOOSECOLS(array,1)=b))))),1)),"")))

 

[Dazzawm]

Try this (replace A2:A16 with your actual Code range and B2:B16 by your actual Transmission range):

=LET(x,A2:A16,y,B2:B16,code,UNIQUE(x),array,HSTACK(BYROW(code,LAMBDA(a,TEXTJOIN(" & ",TRUE,FILTER(y,x=a)))),code),headers,UNIQUE(HSTACK(CHOOSECOLS(array,1))),TRANSPOSE(IFERROR(HSTACK(headers,DROP(REDUCE("",headers,LAMBDA(a,b,VSTACK(a,TRANSPOSE(FILTER(CHOOSECOLS(array,2),CHOOSECOLS(array,1)=b))))),1)),"")))

Thanks but I think I need a macro in this case as I have thousands of rows with multiple combinations.

 

[hagia_sofia]

The formula should cover for that but of course

 

[Cubist]

Try this code:

Sub TransposeData()
    Dim wsInput As Worksheet, wsOutput As Worksheet
    Dim dict As Object
    Dim trans As Variant
    Dim dataRange As Range
    Dim lastRow As Long, i As Long, nextCol As Long, nextRow As Long
    Dim inputArray As Variant
    Dim outputArray() As Variant
    Dim maxRows As Long, maxCols As Long

    Set dict = CreateObject("Scripting.Dictionary")
    Set wsInput = ThisWorkbook.Sheets("Sheet1") ' Input sheet name
    Set wsOutput = ThisWorkbook.Sheets("Sheet2") ' Output sheet name
    lastRow = wsInput.Cells(wsInput.Rows.Count, "A").End(xlUp).Row
    Set dataRange = wsInput.Range("A2:B" & lastRow)
    inputArray = dataRange.Value
    
    For i = 1 To UBound(inputArray, 1)
        trans = inputArray(i, 2)
        If Not dict.Exists(trans) Then
            dict(trans) = ""
        End If
        dict(trans) = dict(trans) & inputArray(i, 1) & ","
    Next i
 
    maxCols = dict.Count
    For Each trans In dict.keys
        maxRows = WorksheetFunction.Max(maxRows, UBound(Split(dict(trans), ",")))
    Next trans

    ReDim outputArray(1 To maxRows + 1, 1 To maxCols)
    nextCol = 1
    For Each trans In dict.keys
        outputArray(1, nextCol) = trans
        nextRow = 2
        For Each cell In Split(dict(trans), ",")
            If Len(cell) > 0 Then
                outputArray(nextRow, nextCol) = cell
                nextRow = nextRow + 1
            End If
        Next cell
        nextCol = nextCol + 1
    Next trans
    
    wsOutput.Range("A1").Resize(UBound(outputArray, 1), UBound(outputArray, 2)).Value = outputArray
End Sub

With a few thousand rows, I think FILTER would be ok. An option for formula.

Book1
	A	B	C	D	E	F	G	H
1	Code	Transmission		CVT	Direct Drive	Manual	Semi-Automatic	Automatic
2	M10105420000001	CVT		M10105420000001	M10105420000003	M10200400000008	M10200400000008	M10200400000012
3	M10105420000002	CVT		M10105420000002	M10200400000013	M10200400000009	M10200400000009
4	M10105420000003	CVT		M10105420000003		M10200400000010
5	M10105420000003	Direct Drive		M10105420000004		M10200400000011
6	M10105420000004	CVT		M10200400000008		M10200400000012
7	M10200400000008	Manual
8	M10200400000008	Semi-Automatic
9	M10200400000008	CVT
10	M10200400000009	Manual
11	M10200400000009	Semi-Automatic
12	M10200400000010	Manual
13	M10200400000011	Manual
14	M10200400000012	Manual
15	M10200400000012	Automatic
16	M10200400000013	Direct Drive
Sheet1

Cell Formulas
Range	Formula
D1:H1	D1	=TOROW(UNIQUE(B2:B16))
D2:D6,H2,G2:G3,F2:F6,E2:E3	D2	=FILTER($A$2:$A$16,$B$2:$B$16=D1)
Dynamic array formulas.

 

[Dazzawm]

Much obliged both.