Forum Discussion
Counting unique entries and creating a list
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.
SergeiBaklan 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.
=UNIQUE(A2:A9) shall return the spill with all unique items, please check the file attached. If not, could you please provide the sample.
SergeiBaklan. 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.
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.
SergeiBaklan . 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.