Solved
Updating status based on set criteria
Posted on 2014-11-20
I am trying to compare "ES NEG POS Date" with "PV DETAILS THIS WK-S Date" and "ES NEG POS Amount" with "PV DETAILS THIS WK-S Amount" and update the status("Fixed" or "Unfixed") based on the below mentioned criteria.
Criteria based on Date
ES NEG POS Date PV DETAILS THIS WK-S Date Status
20-Nov-14 20-Nov-14 Fixed
20-Nov-14 5-Nov-14 Fixed
20-Nov-14 25-Nov-14 Unfixed
Criteria based on Amount
ES NEG POS Amount PV DETAILS THIS WK-S Amount Status
50.00 50.00 Fixed
50.00 40.00 Fixed
-50.00 -40.00 Fixed
0.00 50.00 Unfixed
0.00 -50.00 Unfixed
50.00 90.00 Unfixed
-50.00 -90.00 Unfixed
-50.00 30.00 Unfixed
30.00 -50.00 Unfixed
Status after considering date and amount together
Status based on Date Status based on Amount Final status
Fixed Fixed Fixed
Fixed Unfixed Unfixed
Unfixed Fixed Unfixed
Fixed Unfixed Unfixed
One of my team members is doing this manually for at least 10 sheets. I tried to write “If/And/OR” logic as mentioned below, but was not successful completely. I am finding it difficult because of negative (-) number. Please help
For Date: IF(OR(E2=S2,E2=R2,E2<S2,E2<R2),"Fixed","Unfixed")
For Amount: =IF(T2=0,"Unfixed",IF(M2=T2,"Fixed",IF(AND(T2<0,M2>0),"UnFixed",IF(AND(M2<0,T2>0),"UnFixed",IF(AND(T2<0,M2<0,T2<M2),"Fixed",IF(AND(T2<0,M2<0,M2<T2),"UnFixed",IF(T2>M2,"Fixed",IF(M2>T2,"UnFixed",""))))))))
Hence curious to know if we can write a VB script for accurate results or any other better method I can use to get the right status.
Any suggestions would be greatly appreciated!
I am looking forward for the immediate and positive response!!!!!!
Thanks,
Ganesh