Sep 16 2021 01:32 PM
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!
Sep 16 2021 01:57 PM
See the attached version. The formulas will work in older versions too.
Sep 16 2021 04:08 PM
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)))
Sep 17 2021 12:34 AM
@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.
Sep 17 2021 01:32 AM
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.
Sep 17 2021 02:31 AM
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!
Sep 17 2021 07:38 AM
Sep 17 2021 07:48 AM
@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.
Sep 17 2021 10:16 AM
Thanks for the link to "Off the Grid" material. It looks well presented with good material. I am reasonably familiar with the PQ user interface and can modify the M-code. Despite that, I have the feeling that I am merely paddling in the shallows. There seem to be whole raft of list and table methods available through the use of the Advanced Editor that I have only the sketchiest idea of how they may be exploited.
Sep 17 2021 11:33 AM
@Peter Bartholomew 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.
Sep 17 2021 12:20 PM
Like you, I to tend to restrict VBA to things that worksheet formulae cannot address, such as turning data into sketches or event handling. Nobody ever trusted me with corporate data, so I had little need to analyse pre-existing data. Hence I have tended to follow the development of dynamic arrays more closely.
The changes being introduced there are just about as profound as PQ, I can write solutions with little attention to legacy techniques such as relative referencing or the use of grid coordinates. If I may, I will continue to follow your venture into PQ (maybe M and DAX) and I hope I cause you no offence if I sometimes offer DA alternatives. At present, only a minority of users are in a position to use array methods and, of those who could, many have been put off by the days of CSE.
Sep 18 2021 02:56 PM
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.