Counting Unique Values Across Multiple Columns

Copper Contributor

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 10
  • Excel product name and version number – Microsoft 365 Apps for enterprise
11 Replies

@OliviaBurglund 

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

@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)))

@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.

@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. 

 

@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!

 

Could you provide step by step instructions for this?

@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/ 

@Riny_van_Eekelen 

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.

@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.

 

@Riny_van_Eekelen 

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.

 

Re: A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arr...

 

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.

@Peter Bartholomew 

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.