Unique distinct alphabetically sorted list

**atlant15** · 02-06-2013, 09:12 AM

Hello,

I have a list I wish to sort by alphabetic order.
I also have two criteria though;

Attached is an example file...

I have a list of countries in column A, and a list of sites in column B.
I already made a unique list of countries (column D, in the example) out of the list in column A, and this list is a bridge-step so I can have the countries names as headlines (Row 1, column F-J) – if anyone can offer me a formula to have it directly to the headline spots (without using column D, that will be great as well) – as you can see I left additional 2 spots as headlines (in blue) for future update.

In the yellow section I want to have the unique list of sites per country (alphabetically order), I managed to build a formula that returns every site within the specific country but with duplicated values (with Index formula), I can't find a way to have it as a unique list).

Your assistance is much appreciate.

Thanks.

**benishiryo** · 02-06-2013, 09:20 AM

hi atlant15, try these array formulas. in F1 to copy across:
=IFERROR(INDEX($A$2:$A$25,MATCH(0,COUNTIF($E1:E1,$A$2:$A$25),0)),"")

in F2 to copy below & across:
=IFERROR(INDEX($B$2:$B$25,MATCH(0,IF($A$2:$A$25=F$1,COUNTIF(F$1:F1,$B$2:$B$25)),0)),"")

Edit: Missed out the alphabetical portion.

Addition: changed your formula in D2 to fit into F2:

Formula:

Please Login or Register  to view this content.

**atlant15** · 02-06-2013, 09:55 AM

Thank you,

the additiojnal formula is a bit mixed up, can you take a look again please:
it refers only to column A (The countries list) and genorates only list of countries.