Log In
Register
Home
Active Member
April 27, 2022
Offline
HI Sir/Madam,
Will you please help me to get the formula to find out pure "Not Match" result" I have attached the excel.
How to find out pure not match result. if all results are "Not Match " then result should be "Please verify". if one result is "Match" and one result is "Not Match" then result should be "No need to verify"
| Contract ID | Validation Result | Final Result | Formula |
| CTR100 | MATCH | No Need to verify | |
| CTR100 | MATCH | No Need to verify | |
| CTR100 | MATCH | No Need to verify | |
| CTR200 | NOT MATCH | Please verify (How I can find out pure "Not Match "entries only" formula ?) | |
| CTR300 | MATCH | No Need to verify | |
| CTR300 | NOT MATCH | No Need to verify | |
| CTR400 | NOT MATCH | Please verify (How I can find out pure "Not Match "entries only" formula ?) | |
| CTR400 | NOT MATCH | Please verify (How I can find out pure "Not Match "entries only" formula ?) | |
| CTR500 | MATCH | No Need to verify | |
| CTR500 | NOT MATCH | Please verify (How I can find out pure "Not Match "entries only" formula ?) | |
| CTR500 | NOT MATCH | Please verify (How I can find out pure "Not Match "entries only" formula ?) | |
| CTR600 | MATCH | No Need to verify | |
| CTR700 | NOT MATCH | Please verify (How I can find out pure "Not Match "entries only" formula ?) | |
| CTR700 | NOT MATCH | Please verify (How I can find out pure "Not Match "entries only" formula ?) | |
| CTR700 | NOT MATCH | Please verify (How I can find out pure "Not Match "entries only" formula ?) | |
| CTR900 | MATCH | No Need to verify | |
| CTR800 | NOT MATCH | Please verify (How I can find out pure "Not Match "entries only" formula ?) | |
| CTR111 | NOT MATCH | No Need to verify | |
| CTR111 | MATCH | No Need to verify | |
| CTR111 | MATCH | No Need to verify |
Thanks a Million,
Amol
July 16, 2010
Offline
Hi Amol,
Welcome to our forum! Try this formula in cell D2 of your file, then copy down:
=IF(COUNTIF($B$2:$B$21,"Not Match")=COUNTA($B$2:$B$21),"Please verify","No need to verify")
Mynda
Active Member
April 27, 2022
Offline
HI Madam,
Thanks for swift reply,
After adding the formula in D2 , i am getting all result as "No Need to verify" against all results.
In first column some ID's are repeated and in second column their results are not same. i want to find out wherever comment mentioned in column "C" as "How I can find out pure "Not Match "entries only" formula ? "Please verify".
| Contract ID | Validation Result | Final Result | Formula? |
| CTR100 | MATCH | No Need to check | No need to verify |
| CTR100 | MATCH | No Need to check | No need to verify |
| CTR100 | MATCH | No Need to check | No need to verify |
| CTR200 | NOT MATCH | How I can find out pure "Not Match "entries only" formula ? | No need to verify |
| CTR300 | MATCH | No Need to check | No need to verify |
| CTR300 | NOT MATCH | No Need to check | No need to verify |
| CTR400 | NOT MATCH | How I can find out pure "Not Match "entries only" formula ? | No need to verify |
| CTR400 | NOT MATCH | How I can find out pure "Not Match "entries only" formula ? | No need to verify |
| CTR500 | MATCH | No Need to check | No need to verify |
| CTR500 | NOT MATCH | How I can find out pure "Not Match "entries only" formula ? | No need to verify |
| CTR500 | NOT MATCH | How I can find out pure "Not Match "entries only" formula ? | No need to verify |
| CTR600 | MATCH | No Need to check | No need to verify |
| CTR700 | NOT MATCH | How I can find out pure "Not Match "entries only" formula ? | No need to verify |
| CTR700 | NOT MATCH | How I can find out pure "Not Match "entries only" formula ? | No need to verify |
| CTR700 | NOT MATCH | How I can find out pure "Not Match "entries only" formula ? | No need to verify |
| CTR900 | MATCH | No Need to check | No need to verify |
| CTR800 | NOT MATCH | How I can find out pure "Not Match "entries only" formula ? | No need to verify |
| CTR111 | NOT MATCH | No Need to check | No need to verify |
| CTR111 | MATCH | No Need to check | No need to verify |
| CTR111 | MATCH | No Need to check | No need to verify |
July 16, 2010
Offline
Hi Amol,
Unfortunately, your question isn't clear. What has the first and second column data got to do with your formula? You originally said:
"if all results are "Not Match " then result should be "Please verify". if one result is "Match" and one result is "Not Match" then result should be "No need to verify"."
There was no mention of how the Contract ID and Validation Result impacted your result.
You started to explain in your second post:
"In first column some ID's are repeated and in second column their results are not same. i want to find out wherever comment mentioned in column "C" as "How I can find out pure "Not Match "entries only" formula ? "Please verify"."
But still you don't explain with an example of your desired result and how the ID affects what you want.
Please clarify with an example of what you'd expect to see in the formula column and why.
Thanks,
Mynda
Active Member
April 27, 2022
Offline
Hi Madam,
Apologies for confusion.
in first column "Contract ID" are repeated/single somewhere. in second column "B" "validation result" are mentioned "Match or not Match". which is depend on column "A" Contract ID.
Now will you please help me to find out, How to find out pure not match result. if all results are "Not Match " in column "B" against mentioned contract ID (whether its single or repeated ) in column "A" then result should be "Please verify". if contract IDs are repeated/single in column "A" and one result (CTR300- Match) is "Match" and one result (CTR300- Not Match) is "Not Match" mentioned in column "B" then result should be "No need to verify".
Any formula?
once again sorry for confusion and Thanks for your help again.
Many Thanks, Amol
July 16, 2010
Offline
Hi Amol,
Thanks for clarifying. I think if your 'Final Result' column had just said 'Please verify' instead of 'How I can find out pure "Not Match"entries only" formula ?' your example would have made sense to me.
You can use this formula:
=IFERROR(IF(MATCH("MATCH"&A2,$B$2:$B$21&$A$2:$A$21,0),"No need to check"),"Please verify")
Mynda
Active Member
April 27, 2022
Offline
HI Madam,
Thanks you very much for formula. However its giving result "Please verify " against all entries.
in first column "Contract ID" are repeated/single somewhere. in second column "B" "validation result" are mentioned "Match or not Match". which is depend on column "A" Contract ID.
Now will you please help me to find out, How to find out pure not match result. if for any particular entry in column "A" and their all results are "Not Match " in column "B" against mentioned contract ID (whether its single or repeated ) in column "A" then result should be "Please verify". if contract IDs are repeated/single in column "A" (Please refer 5 and 6 entries) and one result (CTR300- Match) is "Match" and one result (CTR300- Not Match) is "Not Match" mentioned in column "B" then result should be "No need to verify". in column" C" i have added expected result. same result formula should calculate.
Again many Thanks for help and sorry for bothering you. However i need this. hence may i request for help.
| Contract ID | Validation Result | Expected result |
| CTR100 | MATCH | No Need to verify |
| CTR100 | MATCH | No Need to verify |
| CTR100 | MATCH | No Need to verify |
| CTR200 | NOT MATCH | Please verify |
| CTR300 | MATCH | No Need to verify |
| CTR300 | NOT MATCH | No Need to verify |
| CTR400 | NOT MATCH | Please verify |
| CTR400 | NOT MATCH | Please verify |
| CTR500 | MATCH | No Need to verify |
| CTR500 | NOT MATCH | No Need to verify |
| CTR500 | NOT MATCH | No Need to verify |
| CTR600 | MATCH | No Need to verify |
| CTR700 | NOT MATCH | Please verify |
| CTR700 | NOT MATCH | Please verify |
| CTR700 | NOT MATCH | Please verify |
| CTR900 | MATCH | No Need to verify |
| CTR800 | NOT MATCH | Please verify |
| CTR111 | NOT MATCH | No Need to verify |
| CTR111 | MATCH | No Need to verify |
| CTR111 | MATCH | No Need to verify |
1 Guest(s)
