Excel find and count unique values in 2 dimensional array

Question

I have arrays of excel data 3 cols x 300 rows - example

A	B	C
UNIT_TESTING	REMOTE_TEAM_AVATARS	SOCIAL_TIME
SOCIAL_TIME	ELIMINATE_LONG_LIVED_FEATURE_BRANCHES

There will be blanks in some rows.

My goal among the 900 individual cells, find the unique values. Once I have the values displayed I want to count how many instances there of each unique value.

In the trivial case above the result would be:

A	B
SOCIAL_TIME	2
ELIMINATE_LONG_LIVED_FEATURE_BRANCHES	1
...

In an ideal world I want to avoid creating a mid calc column of 900 elements

Stack Overflow is about the facts, non related humor or salutations should not be part of the question. — Scott Craner
– Scott Craner, Commented Nov 29, 2021 at 20:57
On StackOverflow - somehow this seems sad. We all need to smile a little more often. However I will abide. — Mark Levison
– Mark Levison, Commented Nov 29, 2021 at 21:01

Scott Craner · Accepted Answer · 2021-11-29 21:04:35Z

6

With Office 365 we can use UNIQUE, FILTER and SEQUENCE to get the desired output:

=LET(
    rng,    A1:C2,
    clm,    COLUMNS(rng),
    ct,     ROWS(rng)*clm,
    arr,    INDEX(rng,INT(SEQUENCE(ct,,1,1/clm)),MOD(SEQUENCE(ct,,0),clm)+1),
    flt,    FILTER(arr,arr<>""),
    unq,    UNIQUE(flt),
    SORT(CHOOSE({1,2},unq,COUNTIF(rng,unq)),{2,1},{-1,1}))

answered Nov 29, 2021 at 21:04

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mark Levison Over a year ago

Wow. Irony I had spent approx an hour on this so far. I knew a few pieces and in a few minutes you managed to do the whole thing and make it readable. Hat tip.

P.b Over a year ago

Nice one. I didn't get further than doing it in two steps: first get the unique values with

=LET(a,FILTER(A:A,A:A<>""),b,FILTER(B:B,B:B<>""),c,FILTER(C:C,C:C<>""),ra,ROWS(a),rb,ROWS(b),rc,ROWS(c),rabc,ra+rb+rc,unq,UNIQUE(IF(SEQUENCE(rabc)<=ra,a,IF(SEQUENCE(rabc)<=ra+rb,INDEX(b,SEQUENCE(ra+rb,,-ra+1)),INDEX(c,SEQUENCE(rabc,,-ra-rb+1))))),unq)

and then next to it have a count if reference the spill range E1# in my case: =COUNTIF(A1:C3,E1#)

P.b Over a year ago

I read it through more carefully and this is a briljant way to combine a range of columns into one column dynamically. If I was able to upvote multiple times I would. Could you further explain the sorting logic though?

Scott Craner Over a year ago

@P.b the sort will sort the 2 column array returned by the choose first on column 2(2) Descending(-1), then it will sort on column 1(1) ascending(1). The two arrays in the 2nd and 3rd criteria work together to determine order of sort and method.

P.b Over a year ago

Oh, of course. I thought of it as sorting unq and the countif result separately, but they're joined by choose as one array/range. Now I understand.

user3489967 · Accepted Answer · 2024-03-13 22:03:20Z

3

I know this is a little old, but after searching for an answer to this, I managed to come up with my own solution that I think may be simpler.

I used nested Textjoin and Textsplit and then took Unique values of the resultant array. And sorted for good measure. In cell E1

=SORT(UNIQUE(TEXTSPLIT(TEXTJOIN(";",TRUE,A1:C2),,";")))

Then in cell F1 I used a Countif on the spill range from E1

=COUNTIF(A1:C2,E1#)

edited Mar 13, 2024 at 22:03

answered Mar 13, 2024 at 21:56

user3489967

214 bronze badges

Collectives™ on Stack Overflow

Excel find and count unique values in 2 dimensional array

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related