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?

When you say "filter" what exactly do you mean? You could create a pivot table - that will extract unique items. — Floris, Sep 13 '13 at 14:25
There is still no built in worksheet function to do this. You need to write your own. — Bathsheba, Sep 13 '13 at 14:26
Do you want to filter the data while using Excel or do you want to do so programmatically? — m-oliv, Sep 13 '13 at 14:27
Duplicate? http://stackoverflow.com/q/1429899/2258 (Is it irony that the question asking to remove duplicates is a duplicate?) — Richard Morgan, Sep 13 '13 at 14:36

Mara · Answer 1 · 2018-04-06T12:02:40.317

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.

score 0 · Answer 2 · answered Sep 13 '13 at 14:30

0

Use the Unique records only feature in Advanced Filter.

answered Sep 13 '13 at 14:30

Gary's Student

95,722
10
59
99

score 0 · Answer 3 · answered Sep 13 '13 at 14:30

0

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

Remove Duplicates

answered Sep 13 '13 at 14:30

d'alar'cop

2,357
1
14
18

@pnuts Good point. NOTE TO ALL: Works in general. but see link for some interesting exceptions. – d'alar'cop Sep 13 '13 at 14:37
[A pivot table can overcome this challenge](http://superuser.com/a/644986/100579) related to cell content "types". – Werner Sep 13 '13 at 16:57

score 0 · Answer 4 · edited Oct 13 '17 at 19:02

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.

score -2 · Answer 5 · edited Mar 19 '17 at 06:02

-2

filter your data in spreadsheets

This might prove to be of some help to you.

edited Mar 19 '17 at 06:02

Cœur

37,241
25
195
267

answered Nov 16 '13 at 19:21

ashi200

29
7

Filter all unique items like Google Docs

5 Answers5

Linked