Convert repeating rows into column head

mukeshnic

New Member
Joined
Mar 26, 2009
Messages
19
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
L

Legacy 68668

Guest
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

New Member
Joined
Mar 26, 2009
Messages
19
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.
 
L

Legacy 68668

Guest
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
 

mukeshnic

New Member
Joined
Mar 26, 2009
Messages
19

ADVERTISEMENT

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
 
L

Legacy 68668

Guest
Do you have more than 100 States ?

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

mukeshnic

New Member
Joined
Mar 26, 2009
Messages
19

ADVERTISEMENT

I've less than 50 states
 
L

Legacy 68668

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

A1:B500
 

mukeshnic

New Member
Joined
Mar 26, 2009
Messages
19
Actually, it is dyanamic... depends on import

But it always has two column for sure

Currently i've A1:B13
 
L

Legacy 68668

Guest
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,898
Messages
5,834,295
Members
430,274
Latest member
TME1993

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

Which adblocker are you using?

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
Top