# How to pull data from one sheet to another based on duplicate values in a specific column. Google Sheets

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?

@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"), ))}`

SAMPLE OUTPUT

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. 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. Jul 20, 2022 at 0:15