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

 

[mukeshnic]

Thanks a lot Seiya

It works