List and count unique values (MS Excel, Python, and SQL)

This is a fairly common requirement that until recently had a convoluted solution in Excel. Given a list of values, how do we get a count of the unique values and how do we create a list of unique values?

MS Excel

Start with a list in B2:B20, with B2 as the header and B3:B20 with data. Then, in Excel 365, we count the unique values with

=COUNTA(UNIQUE(B3:B20))        

Even better, suppose the list in column B is a table. Then, we use the below, which will auto-adjust as we add more rows to the table. [Vegetables is the column header for a list of vegetables.]

=COUNTA(UNIQUE(Table1[Vegetables]))        

And we get the list of unique values with

=UNIQUE(Table1[Vegetables])        

SQL

Suppose we have a table Customers with a column fName. Then, we get the count of unique names and the unique names themselves with

select count(distinct fName) "Count of Distinct Names" from dbo.customers;
SELECT distinct fName "Unique Names" FROM dbo.customers;        

Python (with Pandas)

Given a list of vegetables, we use

import pandas as pd
data={"Vegetables":["Spinach","Spinach","Spinach","Kale","Kale","Kale","Kale",
? ? "Potato","Okra","Okra","Carrot","Celery","Arugula","Arugula","Arugula",
? ? "Arugula","Cauliflower","Cauliflower"]}
df=pd.DataFrame(data)
print(df.Vegetables.nunique())
# We can also work without knowing the name of the column. ?See below
print(df[df.columns[0]].unique())        

My previous article: https://www.dhirubhai.net/pulse/excel-large-number-arithmetic-python-tushar-mehta