Unique List

[Salt1972]

I need a formula to create a unique list from a column with duplicates, preferably an alphabetical list. I've tried a formula posted here:

=INDEX($A$2:$A$11,MATCH(0,COUNTIF($A$2:$A$11,"<"&$A$2:$A$11)-SUM(COUNTIF($A$2:$A$11,"="&B$1:B1)),0))

The trouble is that my range is 20,000 rows and my spreadsheet bogs down / stops. Google Spreadsheets has a BEAUTIFUL function called "Unique" that will do this. Is there an Excel equivalent?

Thank you!

Matt

 

[Fazza]

hi, Matt

Superior to formulas, I find, is using SQL. Such as

SELECT DISTINCT field_name
FROM data_reference
ORDER BY 1

In Excel this can be a query table and additionally via Excel there is the option of using ADO. Or in VBA without SQL there are many approaches

If the sort is the opposite way, tag DESC on the end so the last clause becomes : ORDER BY 1 DESC

HTH

 

[Salt1972]

Fazza-

Thank you for your response. I gather there are a lot of ways to accomplish my goal... and while, in some circles, consider myself reasonably adept with Excel, I get here and realize that I know NOTHING. I'm afraid the basis for your suggestions lies beyond my knowledge. Are there people on the board (maybe you?) that would do fee based consult/assist on this?

 

[Fazza]

It isn't how the forum operates, Matt. Asking (more) questions & google might answer all your questions. Mr Excel (business) does offer services & training, I believe.

To search for more info google for Excel external data queries. And look in Excel help. There is a wizard that you can explore, start it with ALT-D-D-N

There are plenty of helpers here, just keep asking.

 

[Trebor76]

Assuming you have Excel 2007 or later, let us know if these four steps help:

1. Copy the entire list to a columns where you want the unique values (in any order you like)
2. Select the list just copied and from the Data Tools tab on the Data ribbon click Remove Duplicates
3. If asked, click the Continue with the Current Selection radio button and then click Remove Duplicates
4. Click OK

A meesage box then appears telling you how many duplicates were removed as well as how many unique items remain.

HTH

Robert

 

[FDibbins]

The trouble is that my range is 20,000 rows and my spreadsheet bogs down / stops. Google Spreadsheets has a BEAUTIFUL function called "Unique" that will do this. Is there an Excel equivalent?

Yes, it can be found under data tab, Data Tools, Remove duplicates

An alternaitive may be to use this non-array formula...
=IFERROR(INDEX($B$4:$B$49,MATCH(0,INDEX(COUNTIF($D$4:D4,$B$4:$B$9),0,0),0)),"")