Forum Discussion

175429's avatar
175429
Copper Contributor
Oct 28, 2024
Solved

Pivot/Transpose Issue

Hello all,   On the left is my current table. I want it to look like the table on the right. (I manually created a sample for reference, but my table has over 7000 rows so I can't manually create t...
excel
175429's avatar
175429
Copper Contributor
Oct 29, 2024

HansVogelaar 

 

Here is a link to a sample of my data:

 

https://docs.google.com/spreadsheets/d/1wA6GFne5oS_SSStF0pwW4u13OYVAJd3Zbqe-ilGDXLc/edit?usp=sharing

 

Thank you

HansVogelaar's avatar
HansVogelaar
MVP
Oct 29, 2024

175429 

As an alternative: a VBA macro.

 

Sub FillOverview()
    Dim s As Long
    Dim m As Long
    Dim t As Long
    Application.ScreenUpdating = False
    Range("H2:N10000").ClearContents
    m = Range("A" & Rows.Count).End(xlUp).Row
    t = 1
    For s = 2 To m
        Select Case Left(Range("A" & s).Value, 2)
            Case "01"
                t = t + 1
                Range("H" & t).Resize(1, 3).Value = Range("C" & s).Resize(1, 3).Value
                Range("N" & t).Value = Range("F" & s).Value
                Range("K" & t).Value = Range("B" & s).Value & ""
            Case "02"
                Range("L" & t).Value = Range("B" & s).Value & ""
            Case "03"
                Range("M" & t).Value = Range("B" & s).Value & ""
        End Select
    Next s
    Application.ScreenUpdating = True
End Sub

See the Visual Basic Editor.

 

Resources