• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unique vs Unique Distinct in Excel

N

navic

Active Member
Yesterday we had a discussion in the company about 'Unique vs Unique Distinct' data.
Opinions are different. (I also found conflicting opinions and results on the internet).
I would like to hear the opinion of longtime Excel users.
How to exactly define the difference between 'Unique' and 'Unique Distinct' data in Excel?
I don't need formulas, just enter the results only in the attached file.
 

Attachments

  • uniquevsuniquedistinct.xlsx
    8.4 KB · Views: 9
It means exactly the same thing. No difference.
Both should hold 1,2,3,4,5,6.
Though "Unique Distinct" is just saying same thing twice.

Ex:
In SQL query "SELECT DISTINCT [Column] From Table1" will extract all different values from a column.
In Excel. New "UNIQUE" function will extract all different values from a range.
 
Last edited:
Hi @Chihiro
Thank you for reporting
I don't have an Excel version, where I could try the new "UNIQUE" Excel function. (I use Excel 2013)
But that's why I don't understand when I use "Advanced Filter" for unique data.

My understanding of "Unique vs Unique Distinct" is shown in the tutorial at the link. I want to know if I'm right.
If I'm wrong I want to correct the tutorial.

I wrote:

UNIQUE data = data appear one or more times
DISTINCT data = appear only once

Is this correct? I do not want to give visitors, my wrong information.

My opinion is this:

Data
1
2
3
4
5
3
4
5
6

Unique
1
2
3
4
5
6

Unique Distinct
1
2
6

Am I right?
I would like more "Excel Ninja" to be included in the discussion. I want to hear their opinion as well.
Discussion with my colleagues has grown hot, the bet is active (if I lose, I have to pay for a beer) :(
 
I'd describe the latter as Distinct/Unique value with frequency of 1.

Edit: But really, neither is used in scientific context so each person may have slightly different interpretation.

In following case...
Distinct Count -> counts the number of distinct values in a list.
Unique Count -> counts the number of distinct values that appears only once in list.

FYI - Unique function is added to Office 365 in latest update (v1908).
 
Last edited:
If you needed to define Unique vs. Distinct.

You'd use Distinct, as list of all different values that appear in list.

Then Unique, as list of all different values, that appears only once in list.

But personally, I like to use "frequency" in addition to Unique/Distinct to avoid ambiguity.

Ex:
In ANSI SQL standard, SELECT DISTINCT is valid, but in Oracle specific statement SELECT UNIQUE is valid.
Both returns exactly the same result. So you see what I mean ;)
 
Hi @Chihiro
You'd use Distinct, as list of all different values that appear in list.
Then Unique, as list of all different values, that appears only once in list.
Click to expand...
OK, so this my statement that I wrote below, is not correct?
UNIQUE data = data appear one or more times
DISTINCT data = appear only once
Click to expand...
 
Hi @Marc L
I'm confused or I do not understand well?
Can you type the results attached to the file in the first post, please.
Regards
 
Using manually an advanced filter results are exactly the same as your post #3 (just needs to rename the headers after) …​
​
 
Column D is the classic unique advanced filter and a COUNTIF criteria is used for column E :​
​
62772​
​
 
@navic

UNIQUE data = data appear one or more times
DISTINCT data = appear only once
Click to expand...

Should be reversed. Unique in English term means only one. Distinct means different from others. I. E. Unique is more restrictive.
 
Chihiro said:
Should be reversed.
Click to expand...
Thanks

My apologies if I'm bored with my questions again.
I have another question. This data below is in a column.

100
100
10
50
50
20
325
100
700

Can you answer two questions?

1. Display Unique
2. Display Unique Distinct

Just type in the results (no formulas).
 
If thinking in terms of SQL/Excel.
Both should return 10, 20, 50, 100, 325, 700.

If thinking in more restrictive terms.
Both should return 10, 20, 325, 700.

I already explained the former. Latter, since Unique is more restrictive term than Distinct (i.e. Distinct will include all values present in Unique).
Unique list = Unique Distinct list. It would be different if you compare Unique vs Distinct in latter case.

But I've stated, there is no absolute standard that I'm aware of. It will depend on context. In terms of Excel function, SQL statements, both are treated as equivalent.
 
You must log in or register to reply here.
Back
Top