Search code examples
google-sheetsgoogle-sheets-formula

Comparing columns in Google sheets to find count of values not common with rest

I have an example google sheet (actual data I'm processing is sensitive).

I only want the number of people. (eg. X number of people have Red (but not Orange, Green, Blue).

Link to Google sheet so it's easier to understand and try formulas:

Google Sheets Link

Any help is appreciated

Solution

  • Note: Added the below formulas to the Horanimal_copy_Sheet4 tab for easier viewing.

    Step-by Step Instructions:

    1. Grab a list of unique names by entering =UNIQUE($A$2:$A) in cell D5.
    2. Count how many times each name has an entry by entering =BYROW(D5:D,LAMBDA(r,IF(r="","",COUNTIF($A$2:$A,r)))) in cell E5.
    3. To show any person with only one entry (“eg. Person X has value Red but no other colors.”) enter =ARRAYFORMULA(IF(E5:E=1,XLOOKUP(D5:D,$A$2:$A,$B$2:$B,"",0,1),"")) in cell F5. This will return the only color associated with that person and nothing for the people with multiple colors.
    4. Create a column for every possible color in a cell of your choosing (like E2) with =TRANSPOSE(UNIQUE($B$2:$B))
    5. Finally, for each possible color, count the number of times it is the only color associated with a person. In E3 enter =BYCOL(E2:2,LAMBDA(c,IF(c="","",COUNTIF($F$5:$F,c))))

    This setup will work regardless of how many people you have or how many colors you have and will expand to accommodate any additions or contract for any removals automatically.

    To recap... We go from raw list (which can continue to grow vertically): enter image description here

    To a list of all individuals, how many colors they're associated with, and if only associated with 1, which color that is (which can also continue to grow vertically and be any length): enter image description here

    To finally arrive at a count of how many times a color appears as the only color associated with a person: (which can expand horizontally as colors are added) enter image description here

    Expansion example: When I add an entry for "Horanimal" and "Purple" at the end of cols A & B the tables automatically expand... enter image description here

    If I add another entry for Horanimal with a different color you'll see the count for Purple drops from 1 to 0 because there are no longer any people just in Purple: enter image description here

    Hope that helps!