Google Sheets show only one unique row using Query

Question

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

Only show one occurrence of a duplicate in the combined sheet - If I can do this using query, it will allow me to having other matching criteria as well — Grimlockz
– Grimlockz, Commented Apr 2, 2019 at 16:18

player0 · Accepted Answer · 2019-04-02 17:34:45Z

1

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)))

answered Apr 2, 2019 at 17:34

player0

131k14 gold badges92 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Grimlockz Over a year ago

Thanks, this looks like an awesome query - let me test it out - {2,3,4,1} what does this refer to in the vlookup?

player0 Over a year ago

{2,3,4,1} - order of columns to get from vlookup

Grimlockz Over a year ago

So if I need to extend the columns being pulled across from Combined to A:Z , do I need to update the order of the columns as well?

player0 Over a year ago

yeah, you will need to

Collectives™ on Stack Overflow

Google Sheets show only one unique row using Query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related