Hello, I am trying to count unique values across multiple columns. I am exporting student activities from my work database. You can see from my example how the database exports this information. I would like to be able to count how many people participated in each activity. I have attached an example of the data and also my desired outcome. Thank you in advance! Device and OS platform - PC/Windows 10Excel product name and version number – Microsoft 365 Apps for enterprise

PeterBartholomew1 Hi Peter, it's one of many sites I found just over a year ago. And it helped me to get started with PQ. I moved to an iMac over 10 years ago, because I was frustrated by the many performance issues I experienced on PC's. Start-up a Mac and it works. ALL the time. Though, I did miss PQ's introduction in 2013, but now run W10 and Excel on a 9 years old MacBook Pro via Parallels. Just great. Am amazed that PQ isn't more widespread after 8 years. So many out (t)here think that VBA is the only way out of "complex" problems. True, VBA can do much, but in most cases one can do without. And VBA is difficult for most to become really good at, whereas PQ is easy to become reasonably good at without really scratching the surface of M. And I understood that it can do magic once you really know what you are doing. Am not there yet myself, I'm afraid. At the moment, when I face a somewhat complex problem, my initial thought is "can I solve it with PQ?". If not, I try harder to find a PQ / M solution anyway. If I still can't solve it, only then I will revert to traditional or the more modern Excel functions and perhaps VBA.

PeterBartholomew1 By the way, M and modern Excel functionality have many common. For example, if you remember Fibonacci lambda FIB= LAMBDA(n, IF(n<=2, SEQUENCE(n,,0), LET(b, FIB(n-1), IF( SEQUENCE(n)<n, b, INDEX(b,n-1)+INDEX(b,n-2)) )) ); same FIB in M looks similar ( n ) => [ fn=(j) => let b = @fn(j-1) + @fn(j-2) in if j < 2 then j else b, res = List.Transform({0..(n-1)}, fn ) ][res] DAX in particular and tabular modeling in general is another world, at least from my point of view.

OliviaBurglund See the attached version. The formulas will work in older versions too.

OliviaBurglund As a variant, perhaps you want to consider PowerQuery. Connect to the table with activities, flatten it with "unpivot" and group (count) by activity. Just a few clicks. No complicated formulae needed. See attached.

Counting Unique Values Across Multiple Columns

11 Replies

PeterBartholomew1
Silver Contributor
Sep 17, 2021
OliviaBurglund
For what it is worth, I have got a 365 solution working with beta release functions, so it is probably a little early to be useful.
The main formula is
= LET( fullList, REDUCE("",ActivityGrid, LAMBDA(acc,val,APPENDλ(val,acc))), SORT(UNIQUE(FILTER(fullList, LEN(fullList)>1))))
which runs across each row adding the value to a list. Then it filters out the blanks, removes duplicates and sorts the list to alphabetic order. It does however call a second Lambda function APPENDλ which I defined to be
= LAMBDA(value,list, LET( n, COUNTA(list), k, SEQUENCE(n+1), IF(k<=n,list,value) ) )
Just for the hell of it, I turned the first formula into a named Lambda function so the two formulas for the result were
= UNPIVOTλ(ActivityGrid) = COUNTIFS(ActivityGrid, UNPIVOTλ(ActivityGrid))
This is all to be standard Excel, just not quite as we have known it over the years!
Riny_van_Eekelen
Platinum Contributor
Sep 17, 2021
OliviaBurglund As a variant, perhaps you want to consider PowerQuery. Connect to the table with activities, flatten it with "unpivot" and group (count) by activity. Just a few clicks. No complicated formulae needed. See attached.
Activity Count Question Example-2.xlsx20 KB
- OliviaBurglund
  Copper Contributor
  Sep 17, 2021
  Could you provide step by step instructions for this?
  - Riny_van_Eekelen
    Platinum Contributor
    Sep 17, 2021
    OliviaBurglund If you are totally new to PowerQuery, the link below could be a good start. Chapter 13 deals with unpivoting data. In Chapter 19, you'll learn about grouping and summarising.
    It's a bit of a learning curve, but it's worth it. I promise.
    https://exceloffthegrid.com/power-query-introduction/
- PeterBartholomew1
  Silver Contributor
  Sep 17, 2021
  Riny_van_Eekelen
  Unpivot other columns is tailor-made for the job! The grouping also works effectively.
  I will have to think further on the questions of 'Does a complicated formula cease to be complicated merely because one can select it at the click of a button?' I can see arguments both ways!
  
  I actually have Charles Williams's FastExcel add-in, so I could have gone for
  = LET( fullList, INDEX(UNPIVOT(data,,1,,,0),,3), COUNTIFS(ActivityGrid, SORT(UNIQUE(fullList))))
  Despite that, I think I have yet to find the definitive dynamic array solution for unpivotting that does not rely upon add-ins. Playing with indices works but is somewhat inelegant.

PeterBartholomew1

Silver Contributor

Sep 16, 2021

OliviaBurglund

A 365 solution will no pretence at being interested in legacy Excel!

= LET(
  padded, ActivityGrid&REPT(" ", 10-LEN(ActivityGrid)),
  concatenated, CONCAT(padded),
  k, SEQUENCE(20,1,1,10),
  fullList, TRIM(MID(concatenated,k,10)),
  list, SORT(UNIQUE(FILTER(fullList, LEN(fullList)>0))),
  IF({1,0}, list, COUNTIFS(ActivityGrid, list)))

Activity Count Question Example.xlsx14 KB

HansVogelaar
MVP
Sep 16, 2021
OliviaBurglund

See the attached version. The formulas will work in older versions too.
Activity Count Question Example.xlsx13 KB

Forum Discussion

Counting Unique Values Across Multiple Columns

11 Replies

Resources