Forum Discussion
Converting two columns of stacked data into multiple columns
Hi there,
I thought this would be simple, but I can't figure it out.
I have stacked data of office location against town:
Town A - Office 1
Town A - Office 2
Town A - Office 3
Town B - Office 4
Town B - Office 5
Town C - Office 6
.... etc.
I want to transpose it, with the towns across the top and then a list of offices under each town. Some have 1 office, one has 30.
Any suggestions?
Sub town() Dim lngzeilemax As Long Dim i As Long Dim k As Long Dim j As Long Range("F:MM").Clear j = 6 k = 3 lngzeilemax = Range("C" & Rows.Count).End(xlUp).Row For i = 3 To lngzeilemax If Cells(i, 3).Value = Cells(i + 1, 3).Value Then Cells(2, j).Value = Cells(i, 3).Value Cells(k, j).Value = Cells(i, 4).Value k = k + 1 Else Cells(2, j).Value = Cells(i, 3).Value Cells(k, j).Value = Cells(i, 4).Value k = 3 j = j + 1 End If Next i End SubMaybe with these lines of code. In the attached file you can click the button in cell B2 to run the macro. The assumption of this suggestion is that the values in column C are sorted.
5 Replies
Sub town() Dim lngzeilemax As Long Dim i As Long Dim k As Long Dim j As Long Range("F:MM").Clear j = 6 k = 3 lngzeilemax = Range("C" & Rows.Count).End(xlUp).Row For i = 3 To lngzeilemax If Cells(i, 3).Value = Cells(i + 1, 3).Value Then Cells(2, j).Value = Cells(i, 3).Value Cells(k, j).Value = Cells(i, 4).Value k = k + 1 Else Cells(2, j).Value = Cells(i, 3).Value Cells(k, j).Value = Cells(i, 4).Value k = 3 j = j + 1 End If Next i End SubMaybe with these lines of code. In the attached file you can click the button in cell B2 to run the macro. The assumption of this suggestion is that the values in column C are sorted.
OliverScheurich belated thanks for this, I've been off work for a few days, but that worked perfectly 🙂
If you have Microsoft 365 or Office 2021, you can use the UNIQUE and FILTER functions - see the attached sample workbook.
HansVogelaar thanks for the reply. I should have stated I'm on Office 2013 😞
