google-sheetsfiltergoogle-sheets-formulauniquecountif

Filter based on Unique Values that only match certain criteria


This may be beyond my skill level in Google Sheets, and it's certainly straining my brain to think through, but I have two columns out a large spreadsheet (30000 lines or so) that I need to find matches between unique values on one list, and non-unique but specific values ONLY on another list. That is, I would need the following list to return only the values on the left that had a 3 in the right column every time that value appears on the left, not just for a specific instance.

"Unique" Identifier (can repeat) Value
1 2
2 3
3 2
4 2
5 3
6 2
1 2
2 2
3 2
4 2
5 2
6 2

I have the following formula from another couple answers mocked up, but it doesn't get me all the way there:=UNIQUE(FILTER(A2:A,B2:B>0))

How can I get it to exclude the ones that have, for instance, both a 2 and a 3 in the right column for the same value in the left column?

Edit: To put it in more real terms (I was trying to keep it abstract so I could understand the basics), I have a Catalog ID and a Condition for items, and need to find all Catalog IDs that only have Good copies, not any Very Good copies. This link should show what I want to achieve: https://docs.google.com/spreadsheets/d/e/2PACX-1vSjenkDS2Mk3t4kTcDoJqSc8AV6ONu4Q17K1HPaIUdJkb7dhdnbAt-CzUxGO3ZoJISNpGajUtFTGz8c/pubhtml?gid=0&single=true


Solution

  • to return only the values on the left that had a 3 in the right column every time

    try:

    =UNIQUE(FILTER(A:A; B:B=3))
    

    update 1:

    =UNIQUE(FILTER(Sheet1!A:A; Sheet1!B:B="Good"))
    

    update 2:

    =UNIQUE(FILTER(Sheet1!A:A, Sheet1!B:B="Good", 
     NOT(COUNTIF(FILTER(Sheet1!A:A, Sheet1!B:B<>"Good"), Sheet1!A:A))))
    

    enter image description here