Converting two columns of stacked data into multiple columns

[stevod]

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? 

[#2]

@stevod 

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

[#3]

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

[#4]

@stevod 

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

[#5 · Accepted solution]

@stevod 

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 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.

[#6]

@OliverScheurich belated thanks for this, I've been off work for a few days, but that worked perfectly :)