How to find duplicate values in one column while checking if a condition in another column is met

[Ramadan2512]

I work on a sheet where i neecd to check if the value is duplicated in the same column "E" and if date for both duplicated value in clolumn "H" is =>TODAY() then return value as "Valid Stream" otherweise keep it clear

i have reatched this formula but unfortunately it has one issue I don't know how ot fix it - it gives me ture if only date in one of the duplicated value is=>TYDAY() not date for both values

=IF((COUNTIF($E$5:$E$100,$E$5:$E$100)>1)*($H$5:$H$100>=TODAY()),"Valid stream","")

can you please check and fix my error

 

[hagia_sofia]

Hello, please test:

=IF(COUNTIFS(E5:E100,E5:E100,H5:H100,">="&TODAY())>1,"Valid Stream","")

 

[Ramadan2512]

=IF(COUNTIFS(E5:E100,E5:E100,H5:H100,">="&TODAY())>1,"Valid Stream","")

Thank you so much hgia_sofia for your Help, however when i test it in separate sheet still i get the same error- it give me "Valid Stream" even if only one match has a date >=TODAY() whie i need to be so if both dates for matches are >=TODAY() - Sorry I forgot to say that my worksheet is a table to a range- so when i take it to my table it gives me #SPILL error - can you please make it for a table (my table has 351 rows so far and I add new row every day)

Thank you in advance

 

[hagia_sofia]

Thanks for the feedback and it is indeed my mistake; would this work then:

=IF(AND(COUNTIFS($E$5:$E$100,E5,$H$5:$H$100,">="&TODAY())>1,H5>=TODAY()),"Valid Stream","")

or

=IF(AND(COUNTIFS([CODE],[@CODE],[ISSUE DATE],">="&TODAY())>1,[@[ISSUE DATE]]>=TODAY()),"Valid Stream","")

 

[Ramadan2512]

Thanks for the feedback and it is indeed my mistake; would this work then:

=IF(AND(COUNTIFS($E$5:$E$100,E5,$H$5:$H$100,">="&TODAY())>1,H5>=TODAY()),"Valid Stream","")

or

=IF(AND(COUNTIFS([CODE],[@CODE],[ISSUE DATE],">="&TODAY())>1,[@[ISSUE DATE]]>=TODAY()),"Valid Stream","")

OMG, you are great for 3 days I' trying to reach that result. the first one is working perfectly but the second one appears as text maybe because of my office version 2021 - but one last issue please - when I test the first formula in a small table it works perfectly but when I copy it to my big table it give me empty results - no errors but also no result ... I don't know why

 

[hagia_sofia]

Thanks for the feeback (the second one was meant for an official Excel table because I was not sure whether you are using one or not), that is good to hear. As far the problem is concerned, it is hard to tell: Are your dates formatted as numbers? Are there some extra spaces?

 

[Ramadan2512]

OMG, you are great for 3 days I' trying to reach that result. the first one is working perfectly but the second one appears as text maybe because of my office version 2021 - but one last issue please - when I test the first formula in a small table it works perfectly but when I copy it to my big table it give me empty results - no errors but also no result ... I don't know why

perfect - you are a genius really - i fixed the problem it works perfectly wowwwwww

Thanks for the feeback (the second one was meant for an official Excel table because I was not sure whether you are using one or not), that is good to hear. As far the problem is concerned, it is hard to tell: Are your dates formatted as numbers? Are there some extra spaces?

It's really great hagia_sofia I spent 3 days on Stack Overflow to get such formula and tried hundreds of them and you made it very easy - thank you sooooooooo much

 

[hagia_sofia]

Thank you for the kind words (there are geniuses around here but definitely not me), the most important thing is that the problem has been solved... have a nice Sunday.

 

[Ramadan2512]

hagia_sofia good morning - can you have a look please to my other question

thank you in advance - witshing you a nice day