SOLVED

Counting unique entries and creating a list

Copper Contributor

Is there a straight forward way to get Excel to count the number of unique items in a list and then total how many of each item there are? For example in the list: 4,5,6,7,4,4,5,7 I would like Excel to tell me that there are three 4's, two 5's, one 6, and two 7's.

 

I'm hoping to use this to create a relatively simple inventory counter without having to manually count each item. I am quite competant with Excel and am in the process of learning how to use Macros and VBA if they can be of any use.

 

 

19 Replies
best response confirmed by milanolm (Copper Contributor)
Solution

@milanolm 

There are several ways to achieve that.

One easy way is, you can insert a Pivot Table and drag the same field you are trying to count the unique occurrences in both the Rows area and the Values area and change the value field setting to Count if the column you are working contains the numeric data.

@milanolm 

Yes, Pivot Table is the easiest in pre-DA Excel. When Dynamic Arrays appear that could be

image.png

=UNIQUE(A2:A9)

and

=COUNTIF(A2:A9,UNIQUE(A2:A9))

@Sergei Baklan I just ran across the UNIQUE function and it does exactly what I need, but it seems to only return the first value in my list. For example your "=UNIQUE(A2:A9)" is returning only "4" in the target cell. Ideally, I wanted to do something like "=UNIQUE(A2:C9)", but that is doing exactly the same and returning only the value of A2 in the formula cell. No dynamic array as expected.

@Tasilee 

=UNIQUE(A2:A9) shall return the spill with all unique items, please check the file attached. If not, could you please provide the sample.

@Subodh_Tiwari_sktneer  The pivot table worked exactly like I was hoping. Thanks!

@milanolm 

You're welcome! Glad you found it helpful.

@Sergei Baklan. Strange. The cell C2 in your Excel worksheet that I downloaded with "Save link as ..." is "=_xlfn.UNIQUE(A2:A9)".

 

If I double click the file on the community site, it opens in Google Docs and that same cell is rendered as "=ARRAY_CONSTRAIN(ARRAYFORMULA(UNIQUE(A2:A9)), 4, 1)".

 

I can't make sense of it, so I'll attach the spreadsheet with real data where I am trying to list unique rows. The failing formula "=UNIQUE(M2:O510)" is in cell U2.

 

 

 

 

@Tasilee 

xlfn means your version of Excel doesn't support dynamic arrays, in particular UNIQUE function. So far it's available for Office 365 subscribers on Insiders channels.

https://support.office.com/en-us/article/issue-an-xlfn-prefix-is-displayed-in-front-of-a-formula-882...

 

@Sergei Baklan . I have Office 365, but I re-installed it just to make sure. Excel also accepts the formula "=unique(A1:A9)" so it knows about, but it just places what looks like the A1 value into the formula cell, and no other cells are populated.

 

I have no idea why this behaviour is occuring. If I search in Help for "unique" I get link to use Data |  Sort/Filter | Advanced | Copy to ("B1") Unique records only. If I have in A1:A9

 

a

a

b

c

b

a

d

d

d

 

and I get in B1:B5

 

a

a

b

c

d

 

Even this is not correct. 

 

 

 

 

@Tasilee 

Check all the 'a' values in the range used in the formula. One of 'a' must be having a leading or a trailing space in it and that's why it's picking two 'a' values.

In C1, place the formula =LEN(A1) and copy it down and check the length returned for all the 'a' values and you will find that the length of all the 'a' values is not 1.

 

Or you should try this formula which should return the correct output ignoring any leading or trailing spaces.

=UNIQUE(TRIM(A1:A9))

@Tasilee 

If you have UNIQUE from Excel with Dynamic Arrays, Help shall show something like

image.png

What you speak about that's Advanced filter. It consider first cell in the list as column label. In your case that's A1. Thus first "a" is considered as list name, not as data. More correct will be like

image.png

And word exactly is your Excel channel and build? You may find info in File->Account

image.png

@Subodh_Tiwari_sktneer Thanks, but if I use "=UNIQUE(TRIM(A1:A9))" I get "#value!"

@Sergei Baklan . Thanks Sergei. Still no further in identifying the problem. See attached. "=UNIQUE" is recognized but I am only getting a single entry in the formula cell. I can't work out why it is not working.

 

Capture.jpg

@Tasilee 

This is how it works.

Unique.jpg

 

Please find the attached with the Unique/Trim formula in C2.

 

 

@Tasilee 

I also think that you should have started your own question as it would be irritating for the OP as he/she might be getting all the notifications of replies being posted here and specially when the question has been marked as Solved.

Thanks @Subodh_Tiwari_sktneer . I downloaded the spreadsheet and opened it in Excel and, as previously from Sergei, the formula cell B2 contains "=_xlfn.UNIQUE(A2:A10)". If I click on B2, "{=_xlfn.UNIQUE(A2:A10)}" is actually displayed in the formula bar, but if I click there, the braces { and } dissapear.

@Tasilee 

That simply means you don't have access to the dynamic array functions yet.

Join the Office Insider program from your Account page and keep on updating the Office until you have access to the dynamic formulas.

@Tasilee 

Yes, I agree with @Subodh_Tiwari_sktneer , Dynamic Arrays and all related functions, UNIQUE in particular, are not available for your version. To my knowledge they are deployed only for Insiders Fast and Monthly Targeted channel, not for Monthly channel.

Thanks @Sergei Baklan and @Subodh_Tiwari_sktneer for persevering with this issue. It is unfortunate that the version of Excel I have doesn't complain about the "=UNIQUE()" formula. THat at least would provide a clear indication of the issue.

1 best response

Accepted Solutions
best response confirmed by milanolm (Copper Contributor)
Solution

@milanolm 

There are several ways to achieve that.

One easy way is, you can insert a Pivot Table and drag the same field you are trying to count the unique occurrences in both the Rows area and the Values area and change the value field setting to Count if the column you are working contains the numeric data.

View solution in original post