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:

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

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