Filter all unique items like Google Docs

Question

Is there a quick/easy way to filter all unique items in an Excel 2013 column similar to the Google Docs "Unique" function?

Answer 1

filter your data in spreadsheets

This might prove to be of some help to you.

Answer 2

There isn't an equivalent to =unique() in Excel, and I hate having to work without it.

Without =unique() trying to find all of the unique values in a large array of data is impossible. Take a dozen columns of a hundred+ entries and see what the unique values are across the whole mess and pop them nicely into a new columns. I can't figure out how to do it in Excel, but in Gdocs it's simple:

=unique(transpose(split(ArrayFormula(concatenate(A:M&",")),",")))

Using Filters, or PivotTables, or whatever, just doesn't cut it, and I haven't been able to find any hacked together ridiculous excel formula to do anything similar.

Answer 3

Under the DATA tab there is this: "Remove Duplicates". It'll do what you want.

Answer 4

This is not a pretty answer, but it works. Paste this as an array formula into cell B2:

=LOOKUP(2, 1/((COUNTIF(B$1:B1, A:A)=0)*(A:A<>"")), A:A)

With the column that needs to be filtered in A:A Then drag / copy it down as far as is required.

See it online in Google Spreadsheets

---

Caveats:

• Does not retain original order (resulting order is in fact the reverse)
• Does not automatically expand to cover all cells
• Not fast, not pretty, not transparent

---

Footnotes:

• It is trivial to use IFERROR() to filter out the #N/A errors, but I've not done this to keep the answer concise
• In the same vein the header of the column A is currently also returned. This can be fixed by changing A:A to A$2:$25 in all 3 locations
• Original question was for Excel 2013, all of this should work there, but I wrote and tested it in Excel 2016
• I would love to hear suggestions on how to make the formula automatically expand down as far as required.

Answer 5

Use the Unique records only feature in Advanced Filter.