Log In
Register
Home
Active Member
February 26, 2020
Offline
I need a macro to be able to search and mark in several records since with a formula the expected result is not obtained. I need you to look for the reference number, if that reference is on two or several different dates, you must put the number 2, if a number 1 is one or more times with the same date. Attached example.
Trusted Members
December 20, 2019
Offline
To clarify?
if the reference only appears once = Nothing
if the reference appears twice on the same date = 1
if the reference appears twice on different dates = 2
Would the number show on both rows or just the first?
Purfleet
Trusted Members
Moderators
November 1, 2018
Offline
Since your data appears to be sorted on column A, perhaps this formula in C3:
=IF(OR(A3=A2,A3<>A4),"",IF(COUNTIFS($A4:$A$4914,A3,$B4:$B$4914,"<>"&B3)>0,2,1))
then fill it down.
Active Member
February 26, 2020
Offline
Purfleet Thank you for the answer, if it is duplicated on the same date a number 1 will appear, if it is duplicated on different dates a number 2 will appear. The number will be shown only in the first row
Active Member
February 26, 2020
Offline
Velouria Thank you for the answer, I have put the formula, but some do not put the number 1, I do not know if I am doing something wrong. I have marked as an example in the attached some cells that appear blank. Thank you.
October 5, 2010
Offline
Hi HR,
There's no attachment in your last post.
Regards
Phil
Active Member
February 26, 2020
Offline
VIP
Trusted Members
June 25, 2016
Offline
Give this a try.
In C2 enter
=IF(COUNTIFS($A$3:$A$4914,A3)>=2,2,1)-COUNTIFS($A$3:$A$4914,A3,$B$3:$B$4914,B3)+1
Sunny
Trusted Members
Moderators
November 1, 2018
Offline
Can you clarify what is missing in that example and why?
1 Guest(s)
