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
HansVogelaar's avatar
HansVogelaar
MVP
Oct 28, 2024

175429 

Can you attach the sample workbook or make it available on Google Drive, OneDrive, Dropbox or similar?

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.