Search code examples
google-sheetsgoogle-sheets-query

Google Sheets show only one unique row using Query

I have a combined tab which pulls in loads of sheets to provide a master sheet but I'm trying to work out a way to show only one occurrence when a duplicate occurs

My thinking is that I need to use query as I need to only set some criteria

Column D contains a unique key for each row but this row will appear in other sheets slightly different with only Cell D being the same value 

=UNIQUE(QUERY(Combined!A2:Z,"select * where D matches '"&TEXTJOIN("|",1,Combined!D2:D)&"'" and (E='Cross' or E='Con') ,0))

I thought I could use matches and unique as above to achieve this but sadly I'm not getting anywhere

Any guidance would be great

Sheet - https://docs.google.com/spreadsheets/d/1P29rAgAkTN4_0XZ5BZ6mYni_foOYzgxey3rp5Jor3mI/edit?usp=sharing

Solution

  • wrap it all in QUERY and query it out further as you want:

    =ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(QUERY(IF(COUNTIF(Combined!D:D, Combined!D:D)>1, 
 {Combined!D:D, Combined!A:C}, ), "select Col1 where Col1 is not null")),
 QUERY(IF(COUNTIF(Combined!D:D, Combined!D:D)>1, {Combined!D:D, Combined!A:C}, ), 
 "where Col1 is not null"), {2,3,4,1}, 0)))

    0