Forum Discussion
stevod
Aug 04, 2022Copper Contributor
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 - Offic...
- Aug 04, 2022
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.
HansVogelaar
Aug 04, 2022MVP
If you have Microsoft 365 or Office 2021, you can use the UNIQUE and FILTER functions - see the attached sample workbook.
stevod
Aug 04, 2022Copper Contributor
HansVogelaar thanks for the reply. I should have stated I'm on Office 2013 😞
- Patrick2788Aug 04, 2022Silver Contributor