Forum Discussion
Counting Unique Values Across Multiple Columns
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.
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.
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.
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.
