I have a two column list of countries with corresponding cities:
| Country | State |
|---|---|
| India | Andaman and Nicobar Islands |
| India | Andhra Pradesh |
| India | Arunachal Pradesh |
| Indonesia | Aceh |
| Indonesia | Bali |
| Indonesia | Bangka–Belitung Islands |
| Iran | Ä€zÌ„ÄrbÄyjÄn-e GharbÄ« |
| Iran | ĪlÄm |
| Iran | Alborz |
I want to convert unique Countries in column1 to headers of a table and all States in column2 to appear under respective Countries headers.
| India | Indonesia | Iran |
|---|---|---|
| Andaman and Nicobar Islands | Aceh | Ä€zÌ„ÄrbÄyjÄn-e GharbÄ« |
| Andhra Pradesh | Bali | ĪlÄm |
| Arunachal Pradesh | Bangka–Belitung Islands | Alborz |
What is the quickest way to achieve this if the list is long?
CodePudding user response：
With Microsoft 365, try below formulas as per attached screenshot-
D3=TRANSPOSE(UNIQUE(A2:A10))
D4=FILTER($B$2:$B$10,$A$2:$A$10=D$3)
Drag D4 cell formula to right as per your need.
