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

Question

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?

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

---

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