google sheets
I want to check create a list in C of all the unique values in A that don't exist in B.
I'm using unique to get the unique values in A, but can't work out how to remove the values that exist in B. I'm sure this is easy but just don't know the right terminology to search for.
Does anyone know the answer?
Without a query:
=unique(filter(A1:A6, B1:B6="SubCatA"))
You can accomplish this in two steps:
Create a new sheet Sheet2 and use the following formula for A1 (or wherever you want the column to begin): =unique(Sheet1!A:A). This will give you a vertical column of simply A, B, C, and D as the unique values in Sheet1's A column.
Combine transpose and filter in the cell adjacent to each cell in Sheet2 as follows: =transpose(filter(Sheet1!B:B,Sheet1!A:A=A1)). Here, you are filtering the second column of your Sheet1 according to whether or not the letter next to in Sheet1 matches the latter next to it in Sheet2. This normally prints vertically, so you use transpose to make it print horizontally, i.e. in the same row as you show above. Then, drag the formula down to apply it to each successive row.
The formulas in Sheet2 end up looking like this:
Best Answer
you can try this:
=UNIQUE(FILTER(A2:A;ISNA(MATCH(A2:A;B2:B2;0))))