1

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

0
  • 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

  • why when I fill in the number A column, the number that should not be the same but instead duplicates are detected? ibb.co/X3srbPQ – Lutfi Creativesys Jan 24 at 3:14
  • @LutfiCreativesys check the fix in your spreadsheet – MARK MY ANSWER Jan 24 at 3:31

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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