too much work ahead of me. how to compare formulas input with each other

Flora Edwards
Flora Edwards used Ask the Experts™
on
i receive worksheets from customers with poorly formulas inputs that has many mistakes.

i need help with a macro that compares formulas inputs (not result) and if different found then colors it in red.

for example

in Column A i have formulas and in Column B i also have formulas
lets say cell A2 has a formula =SUM(Sheet1!E1:F14) and cell B2 has  =SUM(Sheet2!E1:F14)/SUM(Sheet2!D1:D14)
i need the macro to put in C2   1 /SUM(Sheet2!D1:D14)  becuase that is the difference between these two formulas. i do not know if obtaining such result is event possible in Excel?

thank you in advance for your help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Not understanding the problem; do you have an example of the problem that is clearer?
Flora EdwardsMedicine

Author

Commented:
tomfarrar

please see attached file.

please see formula in A2 and formula in B2 of Worksheet Sheet1  and then in C2 is those part of the formula which exists in B2 but does not exist in A2.  basically the difference between the two formula is shown in C2.
book.xlsb
cri

Commented:
Which Excel Version do your customers use? The newer Versions, probably since 2010 but I have to look up, have a basic formula consistency checks which can get quite annoying. And therefore people Switch it off in Options.

If this is not enough, is using a commercial add-in an option? There must be something around like this. Because it if needs to be homebrew I think it will take VBA code and some. If the later, must it be fully automatic? Or simply flag possibles for human final checking?
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Flora EdwardsMedicine

Author

Commented:
thanks.

i use excel 2016

using commercial add-in is not an option. i was looking if someone already have created something and if they are willing to share with me.
Top Expert 2014

Commented:
I'm thinking this solution will resemble a modified Levenshtein distance algorithm, except it will return the difference as characters instead of number of single-character changes.
Top Expert 2014
Commented:
Here's an EE article link to help you familiarize yourself with this problem and its solutions:
http://www.experts-exchange.com/articles/2661/A-Guide-to-Approximate-String-Matching.html
Hi WildCat - That is an interesting request.  I am not sure what the result (C2) does for you once you achieve the result. Perhaps if you explain how you are going to use that result, some of the experts could suggest other options that provide a similar, or maybe, better result.  Just a thought..
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
i have seen once a excel add-in called Spreadsheet Advantage had this functionality on comparing cells to cell based on formulas inputs

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial