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.

       

Resources