I currently have a query returning all data where Particular columns match certain conditions, but I would also like to prevent the return of duplicate data from column1.
| A | B | C | D |
|---|---|---|---|
| 1 | x | y | z |
| 1 | x | z | x |
| 2 | r | z | w |
| 3 | q | y | z |
| 4 | q | t | q |
| 4 | q | u | r |
So far: =QUERY(A:D, "SELECT * WHERE B = 'x' OR 'q'")
Returns:
| A | B | C | D |
|---|---|---|---|
| 1 | x | y | z |
| 1 | x | z | x |
| 3 | q | y | z |
| 4 | q | t | q |
| 4 | q | u | r |
How can I also remove the duplciates in column A? So it displays the below:
| A | B | C | D |
|---|---|---|---|
| 1 | x | y | z |
| 3 | q | y | z |
| 4 | q | t | q |
There are many ways to go about this. Given your exact post information, which runs A:D with no headers, here is one way:
=ArrayFormula(UNIQUE(IFERROR(VLOOKUP(FILTER(A:A,ISNUMBER(SEARCH(B:B,"q|x"))),A:D,SEQUENCE(1,COLUMNS(A:D)),FALSE))))
FILTER forms a limited array of only those items in A:A where the corresponding value in B:B exists within the SEARCH criteria string.
VLOOKUP looks up and returns all four columns of values for each of those, which will return the first row of values encountered for every equal value in the FILTERed A:A values. This will result in exact duplicate information for each matching row.
I used SEQUENCE to return an array asking for the return of columns 1,2,3,4 from the requested range. This parameter could have been entered as {1,2,3,4}. But I find SEQUENCE to be more flexible, for instance if in your actual sheet you want the return of 12 columns and not only 4.
UNIQUE eliminates the duplicates.
And, of course, ArrayFormula is necessary because we are processing a range of values.