# Convert repeating rows into column head

#### mukeshnic

##### New Member
I have an excel spreadsheet containing data in two column layout :

States, Cities

UP, Ghaziabad
UP, Agra
UP, Lucknow
UP, Kanpur
UP, Allahabad
Punjab, Amritsar
Punjab, Ludhiana
Punjab, Jalandhar
Punjab, Patiala

How can I covert it in following layout i.e convert repeating rows into column headings ?

Punjab, UP

Amritsar, Ghaziabad
Ludhiana, Agra
Jalandhar, Lucknow
Patiala, Kanpur
, Allahabad

Please advise if it can be done with the help of VBA function or macro

Last edited:

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
I think you could just copy the range and pastespecial with Transpose = Ture.

If you need it vba, you can just record a macro.

Thanks Seiya!

I believe it is not as simple...

I've States in first column and Cities in second column.

I want the data to be split into as many columns as distinct States (listed in first column) and each column should list respective Cities under these columns.

Right...
try
Code:
``````Sub test()
Dim a, i As Long, b(), t As Long, maxRow As Long
a = Range("a1").CurrentRegion.Resize(, 2).Value
ReDim b(1 To UBound(a, 1), 1 To 100)
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For i = 2 To UBound(a, 1)
If Not .exists(a(i, 1)) Then
t = t + 1 : b(1, t) = a(i, 1)
.add a(i, 1), VBA.Array(1, t)
End If
w = .item(a(i, 1)) : w(0) = w(0) + 1
b(w(0), w(1)) = a(i, ii) : .item(a(i, 1)) = w
maxRow = Application.Max(maxRow, w(0))
Next
End With
Range("d1").Resize(maxRow, t).Value = b
End Sub``````

Thanks again...

I'm getting error "subsript out of range" on following line :

b(w(0), w(1)) = a(i, ii): .Item(a(i, 1)) = w

Do you have more than 100 States ?

try
Rich (BB code):
``ReDim b(1 To UBound(a, 1), 1 To 200)``

I've less than 50 states

OK then can you tell me the address of your data range like

A1:B500

Actually, it is dyanamic... depends on import

But it always has two column for sure

Currently i've A1:B13

OOps
change
Rich (BB code):
``        b(w(0), w(1)) = a(i, ii) : .item(a(i, 1)) = w``
to
Rich (BB code):
``        b(w(0), w(1)) = a(i, 2) : .item(a(i, 1)) = w``

Replies
3
Views
334
Replies
5
Views
1K
Replies
1
Views
312
Replies
3
Views
714
Replies
5
Views
348

Threads
1,219,997
Messages
6,151,372
Members
451,023
Latest member
dwalc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back