Transpose data

[ced_lee]

Hello. I have a sample of data that are in rows but I need to list them into columns.
Basically I need to turn the 1st table into the 2nd table.
I have tried looking for ways to transpose but have not been successful. I hope some guru here can help me out. Note that I do not want to make my file into an XLSM, so VBA is not preferred. Thanks.

 

[Trebor76]

Hi ced_lee,

Welcome to MrExcel!!

See how this goes:

Option Explicit
Sub Macro1()

    Dim i As Long, j As Long, k As Long 'Rows based
    Dim y As Long, x As Long 'Columns based
    Dim wsSrc As Worksheet, wsOutput As Worksheet
    Dim objBarCodes As Object
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    Set wsSrc = ThisWorkbook.Sheets("Sheet1") 'Sheet name containing the data to be transposed (columns A to C). Change to suit if necessary.
    Set wsOutput = ThisWorkbook.Sheets("Sheet2") 'Sheet name for the transposed data. Change to suit if necessary.
    Set objBarCodes = CreateObject("System.Collections.ArrayList")
    
    j = wsSrc.Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    
    For i = 2 To j
        If objBarCodes.Contains(CStr(wsSrc.Range("A" & i))) = False Then
            k = IIf(k = 0, 2, k + 1)
            For x = 0 To 2
                wsOutput.Cells(k, 1).Offset(0, x).Value = wsSrc.Cells(i, 1).Offset(0, x).Value
            Next x
            objBarCodes.Add CStr(wsSrc.Cells(i, 1))
        Else
            For y = 0 To 1
                x = x + 1
                wsOutput.Cells(k, x).Value = wsSrc.Cells(i, 2).Offset(0, y)
            Next y
        End If
    Next i
    
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

End Sub

Regards,

Robert

 

[Cubist]

A formula option:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Barcode	Qty	Discount		Barcode	Qty1	Disc1	Qty2	Dis2	Qty3	Disc3
2	A	1	50%		A	1	50%	2	20%	3	60%
3	A	2	20%		B	4	10%	5	15%	6	20%
4	A	3	60%		C	7	24%
5	B	4	10%
6	B	5	15%
7	B	6	20%
8	C	7	24%
Sheet4

Cell Formulas
Range	Formula
E2:K4	E2	=LET(u,UNIQUE(A2:A8),IFNA(HSTACK(u,DROP(REDUCE("",u,LAMBDA(a,b,VSTACK(a,TOROW(FILTER(B2:C8,A2:A8=b))))),1)),""))
Dynamic array formulas.

 

[ced_lee]

A formula option:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Barcode	Qty	Discount		Barcode	Qty1	Disc1	Qty2	Dis2	Qty3	Disc3
2	A	1	50%		A	1	50%	2	20%	3	60%
3	A	2	20%		B	4	10%	5	15%	6	20%
4	A	3	60%		C	7	24%
5	B	4	10%
6	B	5	15%
7	B	6	20%
8	C	7	24%
Sheet4

Cell Formulas
Range	Formula
E2:K4	E2	=LET(u,UNIQUE(A2:A8),IFNA(HSTACK(u,DROP(REDUCE("",u,LAMBDA(a,b,VSTACK(a,TOROW(FILTER(B2:C8,A2:A8=b))))),1)),""))
Dynamic array formulas.

Hi Cubist
Can you elaborate on your method as i reckon it is what i need? For eg, do I need to select certain ranges? Or do I only enter the formula in E2 and drag?
My dataset has 20k plus of rows/barcodes, so I'll need to know what to do exactly.
Thank you.

 

[Cubist]

Enter formula in E2 only, don't need to drag. Leave enough space for it to spill down and right. Test on a small number of rows first. Once it's working, extend the reference to 20k rows.

PS: PowerQuery is another option and probably a lot more efficient.