I need to figure out how to pull rows based on non unique data in a column to another sheet.

For example in the below table I want to pull rows based on duplicates in column "Number" so in my other sheet I would end up with rows 1 and 3.

Name Color Number
Danny Purple 4
Grace Yellow 12
Bob Pink 4
Peter Green 10

This seems to be easy to do with a unique value but I have been really struggling to figure it out when I need non unique. Am I missing something simple is there a !=UNIQUE option or something like that?

1 Answer 1


@user0 has the key to this in Formula for filtering all duplicate data pairs in Google Sheets. This answer is an application of that solution.

Insert this formula in cell D1:

={"Status"; ARRAYFORMULA(IF(LEN(C2:C5), IFERROR(VLOOKUP(C2:C5, ARRAYFORMULA(SPLIT(QUERY(QUERY(QUERY(C2:C5, "select *", 0), "select count(Col1), Col1 where Col1 is not null group by Col1 order by Col1", 0), "select Col2 where Col1 >1", 0)&"♥Duplicate", "♥")), 2, 0), "No"), ))}



Extract "Duplicates"

=query({A1:D5},"select Col1, Col2, Col3 where Col4 = 'Duplicate'")

Extract Non-Duplicates

=query({A1:D5},"select Col1, Col2, Col3 where Col4 <> 'Duplicate'")

  • I do not need to highlight if it is a duplicate I need to move the full row to a new sheet if it is a duplicate. This is still useful though.
    – Jswazy
    Jul 18, 2022 at 19:46
  • Think of Column D as a necessary evil (a “helper” Column) without which the other queries would not work. On a tangent, this formula does not “move” any data. It will make a copy of the duplicates, but the duplicates will remain in the original list. If you truly want to “move” the data, then you need a script. In any event, if the answer has been useful, perhaps you would consider accepting it.
    – Tedinoz
    Jul 20, 2022 at 0:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.