LinkBack URL
About LinkBacks
Hi,
How do I get unique values in a column? I do not want duplicate entries in my result.
I tried using Filter but not got the output.
Anna
Hi,
How do I get unique values in a column? I do not want duplicate entries in my result.
I tried using Filter but not got the output.
Anna
Are you looking for DISTINCT values or UNIQUE values ?
To remove duplicates you could use the data tool "Remove Duplicates". To get a list of unique values in a column you could make a pivot table. There may be other options based on your data, which would probably require uploading a desensitized version.
If you don't have too much data and there are no empty cells within the data range:
=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))
If you don't have too much data and there might be empty cells within the data range:
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))
If you have a lot of data and there are no empty cells within the data range:
Array entered**:
=SUM(IF(FREQUENCY(MATCH(A1:A1000,A1:A1000,0),ROW(A1:A1000)-ROW(A1)+1),1))
If you have a lot of data and there might be empty cells within the data range:
Array entered**:
=SUM(IF(FREQUENCY(IF(A1:A1000<>"",MATCH(A1:A1000,A1:A1000,0)),ROW(A1:A1000)-ROW(A1)+1),1))
** 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.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks