SOLVED

# Converting two columns of stacked data into multiple columns

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

# Re: Converting two columns of stacked data into multiple columns

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

# Re: Converting two columns of stacked data into multiple columns

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

# Re: Converting two columns of stacked data into multiple columns

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

best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Converting two columns of stacked data into multiple columns

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

# Re: Converting two columns of stacked data into multiple columns

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