Search code examples
excelvbapermutationunpivot

VBA Excel - Permutations based from two columns

I'm looking for a way in VBA to realize a permutation based from two columns. I tried with Ubound but it failed What is the better way to write an efficient code for a large file ?

The default DATA is like that

standard DATA

And the final result that I'm looking for :

final result

Solution

  • A Simple Partial Unpivot

    • The main non-VBA tool to do this is PowerQuery (formerly Get & Transform).
    Option Explicit

Sub UnPivot()
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim srg As Range: Set srg = ws.Range("A1").CurrentRegion
    Dim rCount As Long: rCount = srg.Rows.Count
    If rCount < 2 Then Exit Sub
    
    Dim Data As Variant: Data = srg.Value
    
    Dim r As Long
    Dim c As Long
    For r = 2 To rCount
        For c = 4 To 2 Step -1
            Data(r, c) = Data(r, c - 1)
        Next c
        Data(r, 1) = Data(1, 3)
    Next r
    
    Data(1, 1) = "DATE"
    Data(1, 2) = "TITLE"
    Data(1, 3) = "KPI"
    Data(1, 4) = "VALUE"
    
    Dim drg As Range: Set drg = srg.Offset(, srg.Columns.Count + 1)
    drg.Value = Data
    
End Sub