• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to correct formula formula

D

Deepak Sharma

Member
HI Everyone,

I need "Exists" or "Not" in column D.
IF
B1 > 1 in B:B AND C1's YEAR is different in every row against same Record.
For Eg:-
Row A2, A3, A4, A13 I have highlighted with color.
BUT From A5, it's showing Incorrect answers.

Need to correct below formula :-
=IF(SUMPRODUCT(($B$2:$B$21=B2)*($C$2:$C$21<>C2))>1,"Exists","Not")


Thanks and Regards,
Deepak Sharma
 

Attachments

  • Highlight Duplicates based on two columns.xlsx
    9.4 KB · Views: 6
Hi ,

Before a correct formula is arrived at , can you manually enter what should be the correct outputs in each of the cells D2:D21 ?

Narayan
 
Hi Narayan,

Sorry buddy actually, this time formula is needed. Please see the attached sheet's column E:E for correct answers.

Thanks
 

Attachments

  • Highlight Duplicates based on two columns.xlsx
    9.5 KB · Views: 6
Hi ,

As before , this formula seems to give the same results as you have shown :

=IF(SUMPRODUCT(($B$2:$B$21=B2)*(TEXT($C$2:$C$21, "dd-mm") = TEXT(C2, "dd-mm")))>1,"Exists","Not")

Narayan
 
No actually, In this formula you are comparing with dd-mm, but that can be here and there by one month or one-two days. Thats why I wanted to compare by YEAR only.


Regards,
Deepak
 
Does the below work

IF(AND(SUMPRODUCT(--($B$2:$B$21&TEXT($C$2:$C$21,"yy")=B2&TEXT(C2,"yy"))),COUNTIF($B$2:$B$21,B2)-1),"Exists","Not")
 
Hi,
What I exactly need is :-
Criteria :-
1. If Client name is more than one time and in DATE, Month and Day should be same but Year should be different only then formula should say "Exists" else "Not".

I have attached sheet again.

Regards,
 

Attachments

  • dates.xlsx
    922.8 KB · Views: 5
Yes no issues with any extra column. I just need to highlight them...If it can be done by conditional formatting or by adding any extra columns.


Thanks and Regards,
Deepak Sharma
 
Hi Narayan,

So sorry for that. Please see the attached file again.

Conditions are same as below :-
1. If name gets repeated more than one time in list.
And
2. If Day and Month are same but Year should be different.

So for same year's record one need to highlight but not the second one.

Only then it should say Exists Else Not.

Please see attachment.


Thanks & Regards,
Deepak Sharma
 

Attachments

  • Data (Duplicate).xlsx
    10.3 KB · Views: 2
Hi ,

The data set is too small to confirm whether the formula is the correct one or not.

See the attached file where the revised formula is in column F.

The plain English explanation for the formula is as follows :

If it is the first occurrence of an entry in the Name column , the output is Exists , else if the year is the same the output is NOT , else if the Name is the same and the date and month are the same , the output is Exists , else it is NOT.

Narayan
 

Attachments

  • Data sheet (dummy).xlsx
    10.5 KB · Views: 7
You must log in or register to reply here.
Back
Top