Convert repeating rows into column head

[mukeshnic]

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

 

[Legacy 68668]

I think you could just copy the range and pastespecial with Transpose = Ture.

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

 

[mukeshnic]

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.

 

[Legacy 68668]

Right...
try

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

 

[mukeshnic]

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

 

[Legacy 68668]

Do you have more than 100 States ?

try

ReDim b(1 To UBound(a, 1), 1 To 200)

 

[mukeshnic]

I've less than 50 states

 

[Legacy 68668]

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

A1:B500

 

[mukeshnic]

Actually, it is dyanamic... depends on import

But it always has two column for sure

Currently i've A1:B13

 

[Legacy 68668]

OOps
change

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

to

        b(w(0), w(1)) = a(i, 2) : .item(a(i, 1)) = w