How to count unique column data in an excel sheet

Question

I am using excel sheet and i have data column as shown below:

As we can see that some of the names are duplicate or appeared twice. My question is how can count unique name records or rows associated with each name for summary column.

Out put i am looking for is shown below:

Not sure which formula to use as count is counting all of that data i.e. '7' in this case. How can i use count or any other function to count unique records as shown above?

Answer 1

For anyone like me without O265's lovely Unique & Filter Functions, and who doesnt want to use a pivot table, and there are many ways to do this, but this i have just done this in normal excel.

List of data in Column H, Formula in column O3. Drag down. Highlights your distinct and unique values from H.

=IF(COUNTIF(H:H,H28)=1,"U - "&COUNTIF(H:H,H28),IF(COUNTIF(H$1:H27,H28)=1,"U - "&COUNTIF(H:H,H28),"-"))

Formula is short. You can just do this and drag down. Apply the same principal to your worksheet data wherever it is.

=IF(COUNTIF(H:H,H3)=1,"U",IF(COUNTIF(H$1:H2,H3)=1,"U","-")) 

Similarly, you can just use this formula here (credit goes to this source for this one):

=(COUNTIF($H$1:$H1,$H1)=1)+0

Id like to point out that the above formula is a better formula than mine. It highlights with a "1" (or with a tweak, the value of your choice) the first time any value is seen/spotted on any given list, whether duplicate or unique.

Whereas mine is a bit "more random" when picking up the "unique and distict" values.

Mine gets there in the end, but Extend Office's gets there first, as I think is proper (getting the first time a unqique distict value is spotted/occurs.).

Formula in K5 =IF((COUNTIF($H$5:$H5,$H5)=1)+0=1,"UNIQUE DIST","") and drag down...

You could append/add a normal basic countif after the results to show how many actual times the given value appears if you wanted. :

=IF((COUNTIF($H$5:$H5,$H5)=1)+0=1,"UNIQUE DIST","")&" - "&COUNTIF(H:H,H5)

Answer 2

If names duplicates are removed the following formula can be used: =COUNTIF(B:B,F2)

If duplicates must be removed by formula, MATCH (searches for a specified item in a range of cells, and then returns the relative position of that item in the range.) and SMALL (Returns the k-th smallest value in a data set.) functions can be used as shown.

C$1048576 is used to reference last row number for a big list case.

formulas:
Column A, names sequence
Colunm B, names
Column C, formula =MATCH(B2,B:B,0)
Column D, formula =IF(COUNTIF(C2:$C$1048576,C2)=1,C2,"")
Column E, formula =SMALL(D:D,A2)
Column F, formula =VLOOKUP(E2,A:B,2,0)
Column G, formula =COUNTIF(B:B,F2)

Answer 3

CountA(Unique(D2:D8,,False)) = 5 [Count(Unique(D2:D8)) is the same as False is the default.]

CountA(Unique(D2:D8,,True)) = 3 (once and only once)

Note: the Unique function was released in late 2019 to Office 365. So if you want to use this check your version, not present in 1908, present in 2006.

Edit: It's actually in 2002, I just updated my 1908 machine.

HTH

Answer 4

You can do what you're after with a pivot table.

• Click the Insert tab then select "Recommended Pivot Tables".
• A window will open up prompting you to select the data range. I recommend using a named range for your list and referencing that, but you can just highlight the list directly if you want.
• Once the data range is selected, click "Ok" and new window will open with exactly what you want. A unique values list and a "Count of Column1". It is the default of the recommended pivot tables.

I outlined this because it's easy and fast, but it's important to understand you can make this pivot table yourself from scratch if you learn about pivot tables in general. Pivot tables are often overlooked in Excel as an option.

Lastly, you could get really advanced with Excel Power Queries. Just Google "Excel Power query" and you will be shown all kinds of information on them. They are a close second place in power to manipulate Excel data short of using VBA.

Good luck!