#46 — Find The Category Meeting The Specified Condition

Judith-Excel-Sharing - Aug 16 - - Dev Community

Problem description & analysis:
In the Excel table below, the 1st column is the category and the 2nd column contains detailed data whose values are true or false.

original table
Task: Find the category whose detail data does not have the true value. The expected result will be Andy and Tim.

Solution:
Use SPL XLL to get this done:

=spl("=?.groups(~1;count(~2)).select(#2==0).(#1)",A1:B6)
Enter fullscreen mode Exit fullscreen mode

As shown in the picture below:

result table with code entered
Explanation:
groups() function groups and summarizes data; ~1 represents the 1st child member of the current member; #1 is the 1stcolumn of the table.

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .