CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formulaconditional-formatting
Nick Liu
Nick Liu

Reputation: 125

Detect Duplicate data input on different dates

I have the following data that will be input everyday, Column A = Input Date, Column B = Model #. I am just wondering if I could do a conditional formatting for detecting the same models that is inserted on different days.

enter image description here

For instance, on Date (1/1), I input two A's and one B, since those two A's are inserted on the same day, there is no alarm. But when I insert another A on a different date (1/2), is it possible to set up a conditional formatting for this duplicate entry?

I did try the following code, but then it applies to all three A's.

=countif(B:B,B1)>1

Do I need to combine both column A [Date] and Column B[Model #] so that whenever another A is inserted on a different date, then the conditional formatting is applied.

Thanks!

Upvotes: 0

Views: 274

Answers (2)

Karthikeyan Vedi
Karthikeyan Vedi

Reputation: 1360

Use the below formula Validation (Select B:B as range)

=INDEX(A:A,MATCH(B1,B:B,0))=A1

Or Use Conditional Formatting with the below formula (Select B:B as range)

=INDEX(A:A,MATCH(B1,B:B,0))<>A1

Upvotes: 2

Max Makhrov
Max Makhrov

Reputation: 18717

Yes, you may use conditional formatting with custom formula.

  1. First select range A2:B1000
  2. Go to Format → Conditional Formatting
  3. Use custom formula

This formula should work, paste it as your formula:

=NOT(ISNA(FILTER($A$2:A,$A$2:$A<>$A2,$B$2:$B=$B2)))

The result is similiar to this:

enter image description here

Upvotes: 2

Related Questions