Excel VBA to transpose a sets of rows to columns. Each set of rows may not be of the same size

Question

I have a example table with 3 columns (Sample, Index, Value). In this example, there are 12 samples, 3 possible Index and for each of them a Value. I would like to transpose to columns the Value by Index Notes: For each sample, the number of Index measured (with Value) may be different. The number of Samples & Index may also change for a different dataset.

Please find Image of the original table and the desired transposed table (sorry if my description is inaccurate). Kindly help a VBA code for such a purpose. Thank you.

Original & Desired Transposed Table

I could only do a VBA transpose if each set of rows is of the same size (fixed step size).

Dear @Harun24hr formula is not feasible as data size is big, and dataset (No. of sample and index) may change. Thanks. — newuser20250115
– newuser20250115, Commented Jan 15 at 4:27
Please don't upload images of code/data/errors. Please create MD table with online tool Table Generator. — taller
– taller, Commented Jan 15 at 4:39

rotabor · Accepted Answer · 2025-01-15 05:58:47Z

1

Just the pivot table:

Should be fast on bulky data.

answered Jan 15 at 5:58

rotabor

5,6112 gold badges5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

taller · Accepted Answer · 2025-01-15 17:38:17Z

1

PowerQuery offers a more efficient solution than using a VBA script to transform your table.

Select a cell within the source table and press Ctrl+T to convert it into a structured table (ListObject).
On the Excel ribbon, navigate to Data > From Table/Range (if you skip the first step, this will automatically create a table for you).
Edit the PowerQuery formula as shown below.
Use the Save & Load option in the PowerQuery ribbon to output the results to a new worksheet.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sample", type text}, {"Index", type text}, {"Value", type number}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Index]), "Index", "Value", List.Sum)
in
    #"Pivoted Column"

If you prefer to use a VBA script instead

Option Explicit

Sub Demo()
    Dim oDic1 As Object, oDic2 As Object, rngData As Range
    Dim i As Long, j As Long, sKey As String
    Dim arrData, arrTemp()
    Set oDic1 = CreateObject("scripting.dictionary")
    Set oDic2 = CreateObject("scripting.dictionary")
    Set rngData = Sheets("Source").Range("A1").CurrentRegion ' change sheet name as needed
    ' load table into an array
    arrData = rngData.Value
    For i = LBound(arrData) + 1 To UBound(arrData)
        sKey = arrData(i, 1)
        If Not oDic1.exists(sKey) Then ' get unique list of Sample
            oDic1(sKey) = ""
        End If
        sKey = arrData(i, 2)
        If Not oDic2.exists(sKey) Then ' get unique list of IndexN
            oDic2(sKey) = oDic2.Count + 1
        End If
    Next i
    Dim iCol As Long: iCol = oDic2.Count
    oDic1.RemoveAll
    Dim iColIdx As Long
    For i = LBound(arrData) + 1 To UBound(arrData)
        sKey = arrData(i, 1)
        If Not oDic1.exists(sKey) Then ' init. array for each sample
            ReDim arrTemp(iCol)
            arrTemp(0) = arrData(i, 1)
            oDic1(sKey) = arrTemp
        Else
            arrTemp = oDic1(sKey)
        End If
        iColIdx = oDic2(arrData(i, 2))
        arrTemp(iColIdx) = arrTemp(iColIdx) + arrData(i, 3) ' get sum
        oDic1(sKey) = arrTemp
    Next i

    Sheets.Add ' creat new sheet for ouput
    ' populate header row
    Range("A1") = "Sample"
    Range("B1").Resize(1, iCol).Value = Application.Transpose(oDic2.keys)
    ' write output to sheet
    Range("A2").Resize(oDic1.Count, iCol + 1).Value = Application.Transpose(Application.Transpose(oDic1.items()))
    Set oDic1 = Nothing
End Sub

Microsoft documentation:

Range.Resize property (Excel)

Range.CurrentRegion property (Excel)

Dictionary object

Excel formulas (provided by @Mayukh Bhattacharya)

=PIVOTBY(A2:A29,B2:B29,C2:C29,SINGLE,,0,,0)

edited Jan 15 at 17:38

answered Jan 15 at 4:45

taller

19.7k2 gold badges9 silver badges24 bronze badges

3 Comments

newuser20250115 Jan 15 at 15:21

Many thanks @taller. Both PowerQuery & VBA methods work. Only thing being PowerQuery could be affected by data source worksheet clear & overwriting by existing VBA scripts.

taller Jan 15 at 15:51

The only change you need is to re-create ListObject (eg Table1) after clear and populating the source sheet. It is just a code line.

taller Jan 15 at 17:39

@MayukhBhattacharya Thanks for your comment. I've revised the answer.

newuser20250115 · Accepted Answer · 2025-01-15 15:29:22Z

0

Option Explicit

Sub Demo()
    Dim oDic1 As Object, oDic2 As Object, rngData As Range
    Dim I As Long, j As Long, sKey As String
    Dim arrData, arrTemp()
    Dim cdLR As Long
    Set oDic1 = CreateObject("scripting.dictionary")
    Set oDic2 = CreateObject("scripting.dictionary")
    Set rngData = Sheets("Source").Range("A1").CurrentRegion ' change sheet name as needed
    ' load table into an array
    arrData = rngData.Value
    For I = LBound(arrData) + 1 To UBound(arrData)
        sKey = arrData(I, 1)
        If Not oDic1.exists(sKey) Then ' get unique list of Sample
            oDic1(sKey) = ""
        End If
        sKey = arrData(I, 2)
        If Not oDic2.exists(sKey) Then ' get unique list of IndexN
            oDic2(sKey) = oDic2.Count + 1
        End If
    Next I
    Dim iCol As Long: iCol = oDic2.Count
    oDic1.RemoveAll
    Dim iColIdx As Long
    For I = LBound(arrData) + 1 To UBound(arrData)
        sKey = arrData(I, 1)
        If Not oDic1.exists(sKey) Then ' init. array for each sample
            ReDim arrTemp(iCol)
            arrTemp(0) = arrData(I, 1)
            oDic1(sKey) = arrTemp
        Else
            arrTemp = oDic1(sKey)
        End If
        iColIdx = oDic2(arrData(I, 2))
        Cells(I, 5).Value = Cells(I, 2) ' Copy Index into a temp column
        arrTemp(iColIdx) = arrTemp(iColIdx) + arrData(I, 3) ' get sum
        oDic1(sKey) = arrTemp
    Next I

    ' Remove duplicate Index
    cdLR = Sheets("Source").Cells(Rows.Count, "E").End(xlUp).Row
    Sheets("Source").Range("E1:J" & cdLR).RemoveDuplicates Columns:=1
    
    ' Copy and paste transpose Index rows to Header columns
    cdLR = Sheets("Source").Cells(Rows.Count, "E").End(xlUp).Row
    Sheets("Source").Range("E2:E" & cdLR).Copy
    Sheets("New").Cells(1, 2).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    Sheets("Source").Range("E:E").Clear
    
    ' populate header row
    Sheets("New").Range("A1") = "Sample"
    
    ' Remove this line which populates only single Index header
    ' Sheets("New").Range("B1").Resize(1, iCol).Value = Application.Transpose(oDic2.keys)
    
    ' write output to sheet
    Sheets("New").Range("A2").Resize(oDic1.Count, iCol + 1).Value = Application.Transpose(Application.Transpose(oDic1.items()))
    Set oDic1 = Nothing
 
End Sub

answered Jan 15 at 15:29

newuser20250115

1

2 Comments

newuser20250115 Jan 15 at 15:31

Just a minor touch up to the VBA code originally from @taller to fix the Index header name issue.

Community Jan 15 at 20:40

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Excel VBA to transpose a sets of rows to columns. Each set of rows may not be of the same size

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related