Transform data to tabular format

I send it to a kid Excel / VBA beginner as a training, he answered back with a working solution few minutes later
just with the basics - without an advanced filter - as far your need can be understood
so according to your attachment it can be done without any Dictionary, try this way …

With my idea to directly create the first column just with an easy advanced filter
so the row # is well known then you just need to manage the headers.

For further help attach a sample without any error or you misexplained your need
as the result in your actual attachment does to not match your explanation.

If I doubt, I won't waste time to code anything …

Remove Option Explicit from the module as its VBA procedure has not any Dim codeline …

As obviously you can use an advanced filter to directly create the first column
and use the MATCH worksheet function to check the vertical headers
and the Range.Find method to scan the horizontal headers …

PHP Code: 

Sub Macro1()
    Range("E1").Select
    Selection.CurrentRegion.Clear
    Range("A1").Select
    Selection.Copy
    Range("E1").Select
    ActiveSheet.Paste
    LIGNE = 2
    While Range("A" & LIGNE) <> ""
    VERTI = 2
    Do While Range("E" & VERTI) <> ""
    If Range("E" & VERTI) = Range("A" & LIGNE) Then Exit Do
    VERTI = VERTI + 1
    Loop
    If Range("E" & VERTI) = "" Then
    Range("A" & LIGNE).Select
    Selection.Copy
    Range("E" & VERTI).Select
    ActiveSheet.Paste
    End If
    HORIZ = 6
    Do While Cells(1, HORIZ) <> ""
    If Cells(1, HORIZ) = Range("B" & LIGNE) Then
    If Cells(VERTI, HORIZ) = "" Then Exit Do
    End If
    HORIZ = HORIZ + 1
    Loop
    If Cells(1, HORIZ) = "" Then
    Range("B" & LIGNE).Select
    Selection.Copy
    Cells(1, HORIZ).Select
    ActiveSheet.Paste
    End If
    Range("C" & LIGNE).Select
    Selection.Copy
    Cells(VERTI, HORIZ).Select
    ActiveSheet.Paste
    LIGNE = LIGNE + 1
    Wend
    Application.CutCopyMode = False
End Sub 


Not so long just desactivating the screen updating and playing directly with objects as always,
and just using Excel features as described in my previous post.
You can also calculate how many rows and how many columns are necessary in order to create an array accordingly …

Or first think about a logic on paper and once valid then you can elaborate it here.

I wanna know why you try to use a Dictionary without allocating any data,
if only there is a logic behind - explain it - as maybe I could see a green light rather than a red one …
If it's only for the first column so the Dictionary is useless as an advanced filter can create it just with a single codeline !

Please Login or Register  to view this content.

Before to write any codeline, a paper, a pencil are your best friends.
If you are stuck in a way and in a hurry, just think about what Excel offers …

As it depends on the real workbook size
- difficult to imagine without any information neither an attachment reflecting it in the initial post -
my first idea is an advanced filter to create the first column just with a single codeline
- like any Excel beginner operating manually - and an array to allocate the next columns