CODEWITHSUNDEEP
excelgoogle-sheets
Yaniv Aknin
Yaniv Aknin

Reputation: 4253

Google sheets: count unique items per column, filtering by row key

Suppose I have a sheet showing how workers are using different tools during their day. I'd like to have a separate "sheet of tools" with various data about the tool and a formula computing unique tool users (so without using a pivot table).

For example, consider this table: tool use throughout day

We can see the screwdriver is only used by one worker (alice), the saw is used by all three (alice, bob and carol) and the hammer is used by two (alice and bob).

I then want to have a separate sheet looking like this: tool data and unique users

I've tried using a UNIQUECOUNT & FILTER combination (filter alice's column by a tool's name then uniquely count, add to filtering bob's column and uniquely count, etc), but I have 15 workers and the result is ghastly.

How should I do this well?

Upvotes: 0

Views: 643

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18707

You have so called wide table:

Wide tables have some features:

  1. They may grow down and right.
  2. They give good visual and compact view.

But tradeoff is wide tables are very hard to use in any sort of calculations.

So step1 is to unpivot wide tables in order to convert this:

        alice         bob        carol
hour1   screwdriver   hammer     plyer
hour2   hammer        saw        plyer
hour3   saw           hammer     saw
hour4   plyer         hammer     saw

into this:

hour1   alice       screwdriver
hour1   bob         hammer
hour1   carol       plyer
hour2   alice       hammer
hour2   bob         saw
hour2   carol       plyer
hour3   alice       saw
hour3   bob         hammer
hour3   carol       saw
hour4   alice       plyer
hour4   bob         hammer
hour4   carol       saw

I use my custom unpivot function for this:

enter image description here

=unpivot(A1,A2:A5,B1:D1,B2:D5)

You may find the code of this function here:

The next step is to use some good Google Sheets functions and get the result:enter image description here

=QUERY(UNIQUE(QUERY(unpivot(A1,A2:A5,B1:D1,B2:D5),"select Col2, Col3")), "select Col2, count(Col1) where Col2 <> '' group by Col2 label count(Col1) ''")

Upvotes: 1

Related Questions