Google Sheets – Formula for Filtering Duplicate Data Pairs

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

I'm making a table to check duplicate data.
Where data that wants to be duplicated is in the "Sampel1" column and the duplicate checking results are in the "Status" column.

Table1

The formula that I have made is:

=if(COUNTIF($A$3:A3, A3)>1, "Duplicate", "No")

I want when table 1 is filtered only for duplicates, the results can be seen in the picture below:

Table results that have been filtered

Best Answer

  • paste this in B2:

={"Status"; ARRAYFORMULA(IF(LEN(A3:A), 
  IFERROR(VLOOKUP(A3:A, ARRAYFORMULA(SPLIT(QUERY(QUERY(QUERY(A3:A,
  "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"), ))}

1

Related Topic