• 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.

Conditional Format Macro

arkk

arkk

New Member
Hi All,

I looking for a suggestion and help what will be better in this scenario. I am doing software testing in which I have to compare lots of values Actual vs Expected. I have done macro and attached. The way how I have done this is --- the number of values to compare are not constant (in this case i have taken 5) val1 ... val5 and the corresponding Expected results will be val1Exp ....val5Exp. Currently I have done if the actual is different than expected then it does color coding (CompareAnd Color Macro) pink for Actual mismatch and yellow for Expected mismatch.

Now the Question -
The requirement has changed the values need not have to exactly match they can be a +/- 0.01% Tolerance. So Shall I do this using a conditional formatting Icon sets with formulas? or shall I handle this in the Macro as done now (due to the dynamic nature of the number of values ). Also not all values have the tolerance limit some need to be exact match eg dates for which I have created a config sheet. Any suggestions and help appreciated.
 

Attachments

Depending on your data size. You will experience significant increase in resource usage with CF method and will hamper further analysis.

One way to check for exact match is to utilize Scripting Dictionary. Key property of Dictionary will only allow unique value.

Here's link to good tutorial on Scripting Dictionary.
http://excelmacromastery.com/Blog/index.php/vba-dictionary/

To check for variance, you can do standard arithmetic operation with ABS (so that +/- does not matter).
 
Thanks Chihiro.
The exact match is already done. Looking for the variance stuff. either to handle by formulas or macro or any other etc..
 
See attached. Added ElseIf to check for Tolerance column variance, and UDF checking if column header contains Tol.

Note that column header should be suffixed with "Tol" instead of "Exp" for the columns where you want to check if variance is within tolerance level.

Demo is done on val1Tol vs. val1
 

Attachments

Thanks Chihiro. I liked the idea of handling the Tolerance allowed or not in the prefix of the validation. kool this eliminates the config sheet :)
 
You must log in or register to reply here.
Back
Top