How to generate a unique, alphabetized list of entries from a column of data

[roscoe]

I have a table of entries that could be 100+ entries. Column D of that table is the country of origin. It may or may not repeat throughout the table.

I need to generate a list of countries that appear in that column that is (1) unique [no repeats] and (b) preferably alphabetized.

I may have done something like this in the past, but if so I can no longer find it and now have no clue how to start...

Thanks!

 

[roscoe]

Hi,

Make name range Country (all country) then use this CSE formula:

=IFERROR(INDEX(Country,SMALL(IF(FREQUENCY(MATCH(Country,Country,0),ROW(Country)-MIN(ROW(Country))+1)>0,ROW(Country)-MIN(ROW(Country))+1,""),ROW($A1))),"")

CSE --> CONTROL+SHIFT+ENTER

Hmmm...didn't work. Got entire list of just the first country in the list. Not sure I did it right. I created a random list of counties, then named the range "Country". Then a couple of columns over I highlighted a vertical range of cells, entered the formula above into the address bar and CONTROL+SHIFT+ENTER.

 

[roscoe]

Anything?

 

[FlameRetired]

Hi roscoe,

It's been awhile since 6/2016.

That formula should work ... however ...

Then a couple of columns over I highlighted a vertical range of cells, entered the formula above into the address bar and CONTROL+SHIFT+ENTER.

Committing the entire range at once is what is causing this. Try the following to remedy the above.

1.) Select the range in question and delete it.
2.) Now array enter the formula given into the first cell of the intended range only.
3. )Fill down.

As an added thought. Consider changing

=IFERROR(INDEX(Country,SMALL(IF(FREQUENCY(MATCH(Country,Country,0),ROW(Country)-MIN(ROW(Country))+1) > 0,ROW(Country)-MIN(ROW(Country))+1,""),[B]ROW($A1)[/B])),"")

To

=IFERROR(INDEX(Country,SMALL(IF(FREQUENCY(MATCH(Country,Country,0),ROW(Country)-MIN(ROW(Country))+1) > 0,ROW(Country)-MIN(ROW(Country))+1,""),[B]ROWS($1:1)[/B])),"")

The ROWS($1:1) argument won't cause problems when inserting or deleting rows above the range. The ROW($A1) argument can.

 

[AhoyNC]

This link may also help.

Excel Magic Trick 759: Array Formula To Sort List & Remove Duplicates - Dynamic Named Range
https://www.youtube.com/watch?v=IZLAzIYfMDU

 

[roscoe]

Thanks, but I did enter it as an array formula with CONTROL+SHIFT+ENTER. I'll try the fill down method but pretty sure that's not the issue as I'm familiar with array formulas.

 

[FlameRetired]

By the way. That formula will return unique countries, but they won't be sorted. If you are open to helper columns this non-array approach will do both. There is also an array version of this without helpers.

Named ranges are Country, helper1 and helper2.

Excel 2012
	A	B	C	D
1	Country	helper1	helper2	Sorted Unique
2	Finland	12	4	Argentina
3	Yemen	30	11	Austria
4	United Kingdom	22	9	Canada
5	Iceland	18	7	Finland
6	Greece	16	6	France
7	United Kingdom	FALSE	FALSE	Greece
8	Finland	FALSE	FALSE	Iceland
9	Yemen	FALSE	FALSE	New Zealand
10	Finland	FALSE	FALSE	United Kingdom
11	Argentina	4	1	United States
12	Yemen	FALSE	FALSE	Yemen
13	United States	26	10
14	United States	FALSE	FALSE
15	New Zealand	19	8
16	Yemen	FALSE	FALSE
17	Austria	7	2
18	Greece	FALSE	FALSE
19	Austria	FALSE	FALSE
20	France	13	5
21	Argentina	FALSE	FALSE
22	Greece	FALSE	FALSE
23	United States	FALSE	FALSE
24	Argentina	FALSE	FALSE
25	Iceland	FALSE	FALSE
26	Canada	9	3
27	Argentina	FALSE	FALSE
28	Canada	FALSE	FALSE
29	United States	FALSE	FALSE
30	Austria	FALSE	FALSE
31	United Kingdom	FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
B2	=IF(COUNTIF($A$2:A2,A2)=1,COUNTIF(Country,"<="&Country))
C2	=IF(B2,RANK(B2,helper1,1))
D2	=IFERROR(INDEX(Country,MATCH(ROWS($2:2),helper2,0)),"")

Named Ranges
Name	Refers To	Cells
Country	=Sheet1!$A$2:$A$31
helper1	=Sheet1!$B$2:$B$31
helper2	=Sheet1!$C$2:$C$31

 

[T. Valko]

Here's one way...

Data Range

	B​	C​	D​
1​	Country​	------​	Uniques​
2​	Greece​		China​
3​	France​		England​
4​	USA​		France​
5​	Germany​		Germany​
6​	Greece​		Greece​
7​	Italy​		Italy​
8​	USA​		USA​
9​	England​
10​	China​
11​	China​

This array formula** entered in D2:

=IFERROR(INDEX(B$2:B$11,MATCH(0,COUNTIF(B$2:B$11,"<"&B$2:B$11)-SUM(COUNTIF(B$2:B$11,D$1:D1)),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

 

[roscoe]

Never said thank you but the last option did exactly what I asked for......unfortunately I then discovered that my desire to "update on the fly" was a bad idea. Every edit, every minor change takes 15-25 seconds to recalculate (Which I need because I use conditional formatting to guide my edits) and is driving me CRAZY.

I now think a macro that runs on "file open" and/or "on command" is a better option. I'm going to look at some of the advanced filter ideas that were suggested earlier.