Converting two columns of stacked data into multiple columns

Copper Contributor

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? 



5 Replies


If you have Microsoft 365 or Office 2021, you can use the UNIQUE and FILTER functions - see the attached sample workbook.

@Hans Vogelaar thanks for the reply. I should have stated I'm on Office 2013 :(


Not an ideal arrangement but can be done quickly with a pivot table.



best response confirmed by Hans Vogelaar (MVP)


Sub town()

Dim lngzeilemax As Long
Dim i As Long
Dim k As Long
Dim j As Long


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

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 Sub

Maybe 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 :)