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]

Forgot to add, looking to do this without macro, i.e. with formula, so it would update on the fly as new data is entered.

 

[CsJHUN]

I suggest you should make some try with "data" -> "sort and filter" -> "advanced" -> "unique records only"

 

[BGY23]

Use a pivot table.

Make sure all the columns in the range have a heading. If you Just want a unique list of countries then use just your column D.

High light the table and go to Insert>Pivot table>. If you wnat the pivot table on a new sheet click ok, if you want it on an existing sheet click existing sheet and show it where you want the list of coutries. click ok.

Now in the pivot table field list click the name of the heading on your column D and click ok. The field should show in the ROWS section at the lower part of the pivot table constuction pop up.

Click ok amd you get a list of countries in alphabetical order.

You can get values by country too. Say you had a cloumn in your data range with sales in click the sales box the same way you did country and it will sum the sale by country.

 

[BGY23]

Forgot to say if you extend the rang3e for the pivot table below your existing list as you add new countries they'll be included too. Just click into the pivot table> Right mouse> refresh and the table will be updated with all the coutries (unique) in the area the range covers.

 

[CsJHUN]

Forgot to say if you extend the rang3e for the pivot table below your existing list as you add new countries they'll be included too. Just click into the pivot table> Right mouse> refresh and the table will be updated with all the coutries (unique) in the area the range covers.

Yep, other totally working method. In this case i suggest use =$A:$D (or just =$D:$D) as range of pivot

 

[roscoe]

I've used pivot tables a lot in the past but in my experience they don't update on the fly; you have to actively refresh the table. Also, what I failed to mention is I'm going to make a name range from the resulting list to populate a drop down box (prevents folks from misspelling challenging countries like Kazakhstan and making automated comparisons impossible). Can I do that with a pivot table?

 

[BGY23]

Now we know the full story the solution is to use advance filters.

Decide where you want the list to appear, say G5. Type the heading on your list of names into G5.

Now goto your list and highlight it includng the name, click on Data(ribbon)> Advanced(filters) from the pop up box click copy to another location and lower down in "copy to" put G5. Click unique records. Click ok.

You'll get a list of unique names. Not in alphabeltical order you'll have to sort it.

This list won't be genertaed on the fly either so I suggest you record the above in a macro aand create a button to run the filter and sort.

Good luck

 

[Ingolf]

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

 

[roscoe]

AN array formula...that's what I was hoping for. I'll give that a try. Thanks.