Need urgent help please with if formula with multiple criteria

Hi Guys,

First post on this forum. Great to see so much help out there to enable us to better ourselves. Certainly want to get into Excel more and more, love what it can do.

I am trying to come up with an if formula that will return the following;

Where the consignment note value in column D is the same, (duplicate) and the date in column B is different i need to return the word, "duplicate" and where this criteria is not met return the word, "No". Although the consignment note may be the same, (duplicate) in column D, the catch is that it can only be a duplicate on a different date in column B.

The formula i used was;

=IF(AND(COUNTIF(B$5:$B40,B40)<>B39,COUNTIF(D$5:$D40,D40)>1),"DUPLICATE","NO")

I know it is wrong!!

It is returning the word, "duplicate" for the same consignment (column D) on the same date (column B) as well as different date (column B). I only want it to return, "duplicate" when the date (column B) is not the same.

It also needs to return the word, "duplicate" very each same consignment after the first consignment (column D) on a different date (column B).

See attached file with comments in F39 & F41.

Please help!

Adam

I think it's best that you review your attached spreadsheet! Your comments were not in line with the data itself.

Also, would appreciate if you could have given the "ACTUAL" solution (i.e. duplicate and the reason why).

You were close, unless I'm not getting what you are asking for

E5 drag down:

Please Login or Register  to view this content.

Hi nemo,

I tried your formula, but it didn't seem to work on my end... but a simple tweak might do the trick... otherwise, nice job...

Try this instead... =IF(COUNTIFS($B$5:B5,"<>"&B5,$D$5:D5,D5)>1,"DUPLICATE","NO")

Hi AClarke... let us know if this is what you are looking for...

Dennis

Originally Posted by dluhut

I think it's best that you review your attached spreadsheet! Your comments were not in line with the data itself.

Also, would appreciate if you could have given the "ACTUAL" solution (i.e. duplicate and the reason why).

Today, 03:49 PMdjapigo
Re: Need urgent help please with if formula with multiple criteria

Hi nemo,

I tried your formula, but it didn't seem to work on my end... but a simple tweak might do the trick... otherwise, nice job...

Try this instead... =IF(COUNTIFS($B$5:B5,"<>"&B5,$D$5:D5,D5)>1,"DUPLICATE","NO")

Hi AClarke... let us know if this is what you are looking for...

Dennis Today, 02:26 PMnemo74
Re: Need urgent help please with if formula with multiple criteria

You were close, unless I'm not getting what you are asking for

E5 drag down:


=IF(COUNTIFS($B$5:B5,B5,$D$5:D5,D5)>1,"Duplicate","No")




Hi Guys,

Thanks for your responses!

I have attached the spreadsheet again with formulas provided by Dennis and Nemo (see attached).

I tried the following formulas per the posts from Dennis and Nemo;

Dennis =IF(COUNTIFS($B$5:B5,"<>"&B5,$D$5:D5,D5)>1,"DUPLICATE","NO")
Nemo =IF(COUNTIFS($B$5:B5,B5,$D$5:D5,D5)>1,"Duplicate","No")

Nemo, your formula returned that the same consignment note on the same date was a duplicate which is not what i need.

Dennis, your formula was closer it worked correct in cells E41 & E42, however, it is not correct in E31.

Just to reiterate what i need and sorry if i wasn't clear in my original request;

Where consignment number in column D matches (same number) i need to return the word, "duplicate" only if the corresponding date in column B is different (not the same);

Example 1;

Date (Column B) Consignment (Column D) Duplicate? Comments
29-Jan 12345 No Consignment matches, however, date is same
29-Jan 12345 No Consignment matches, however, date is same

Example 2;

Date (Column B) Consignment (Column D) Duplicate? Comments
30-Jan 12345 No First consignment with same number
31-Jan 12345 Yes Second consignment matches and date is different

Example 3;

Date (Column B) Consignment (Column D) Duplicate? Comments
30-Jan 12345 No First consignment with same number
31-Jan 12345 Yes Second consignment matches and date is different
31-Jan 12345 No Third consignment matches, however, date is same as second

I hope the above examples make sense. Examples 2 and 3 return the outcome i need for the duplicates, if it's not a duplicate it just needs to be, "No".

If someone can please finalise this for me within the next few hours that would be great as i need it today!!

Thanks again!!!

Adam

OK, how about this...

=IF(AND(COUNTIFS($B$5:B13,"<>"&B13,$D$5:D13,D13)>1,COUNTIFS($B$5:B13,"<>"&B13,$D$5:D13,D13)<>COUNTIFS($B$5:$B$42,B13,$D$5:$D$42,D13)),"DUPLICATE","NO")

Originally Posted by djapigo

OK, how about this...

=IF(AND(COUNTIFS($B$5:B13,"<>"&B13,$D$5:D13,D13)>1,COUNTIFS($B$5:B13,"<>"&B13,$D$5:D13,D13)<>COUNTIFS($B$5:$B$42,B13,$D$5:$D$42,D13)),"DUPLICATE","NO")

Thanks Djapigo.

I have re-attached the file with your formula added in column G. In majority it doesn't work - see my comments in column I. Can you please try again, thanks!!

Sorry, I gave a formula from row13... try this instead...

=IF(AND(COUNTIFS($B$5:B5,"<>"&B5,$D$5:D5,D5)>1,COUNTIFS($B$5:B5,"<>"&B5,$D$5:D5,D5)<>COUNTIFS($B$5:$B$42,B5,$D$5:$D$42,D5)),"DUPLICATE","NO")

Originally Posted by djapigo

Sorry, I gave a formula from row13... try this instead...

=IF(AND(COUNTIFS($B$5:B5,"<>"&B5,$D$5:D5,D5)>1,COUNTIFS($B$5:B5,"<>"&B5,$D$5:D5,D5)<>COUNTIFS($B$5:$B$42,B5,$D$5:$D$42,D5)),"DUPLICATE","NO")

Thanks again!

I tried this and some of it worked, some of it didn't......I have attached an amended file with your name where consignment reference has changed to column F instead of D and i updated your formual accordingly. This is the real file i am working off.

Can you please try again!!

Thank you

OK, none of my formulas work... however, nemo's formula works with 1 caveat...

You have to change your dates (col B) to actual dates and not text... (highlight col B, then under "Data" tab, press "Text to Columns", press Next until done)

Afterwards, I believe that is what you want...

Originally Posted by nemo74

Wait wait I just seen the last sheet you posted...going to fix some stuff

Ok, paste in M5 and sort your list by column F Largest to smallest.

Please Login or Register  to view this content.

Done?

Nemo - YOU ARE AN ABSOLUTE LEGEND!!!! FINALLY GOT THERE!!!

I can't thank you enough, saved the day!

Absolute legend!

remember to mark your post as solved and
if you would like to leave some rep press the
<-----STAR.