Forum Discussion
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 the whole thing).
As you can see I have multiple entries per day. When I transpose it in PQ, it shortens it to one entry per day, basically erasing all the other entries.
Help would be greatly appreciated.
Thank you!
Can you attach the sample workbook or make it available on Google Drive, OneDrive, Dropbox or similar?
Here is a link to a sample of my data:
https://docs.google.com/spreadsheets/d/1wA6GFne5oS_SSStF0pwW4u13OYVAJd3Zbqe-ilGDXLc/edit?usp=sharing
Thank you
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 SubSee the Visual Basic Editor.
