Comparing 2 Sheets in a Workbook

1. Comparing 2 Sheets in a Workbook

Hello,

Here is my problem. Any pointer in the right direction is greatly appreciated.

I have 2 sheets in the same workbook:

Sheet 1 has 2 columns: "Factor" and "Threshold". There are many unique factors, each having its own numerical threshold.
Sheet 2 has many columns, relevant being "Company","Factor", "Old Value" and "New Value".

There are many companies, each has different set of Factors, and appropriate Old and New Values.

What I need to do is to go through Sheet 2, check every Factor, check the difference between Old and New Value, and highlight the row where the difference is larger than the threshold for that Factor on Sheet1.

Please let me know if any of this is unclear. Any help is greatly appreciated.

Regards,
Lana

2. Re: Comparing 2 Sheets in a Workbook

Can you attach a sample workbook with sample data and mock-up what you want as your result for that data.

3. Re: Comparing 2 Sheets in a Workbook

Thank you, kersplash! Please see attached. I highlighted rows in sheet 2 and explained why.

4. Hi ! Try this ‼

According to your attachment as a beginner starter :

PHP Code:
``` Sub Demo1()     Dim A\$         A = Sheet1.Range("A2", Sheet1.[B1].End(xlDown)).Address(External:=True)    With Sheet2.[A1].CurrentRegion.Columns("A:D")        .Interior.ColorIndex = xlNone         Application.ScreenUpdating = False        .Range("E2").Formula = "=ABS(C2-D2)>VLOOKUP(B2," & A & ",2,FALSE)"        .AdvancedFilter xlFilterInPlace, .Range("E1:E2")        .Rows("2:" & .Rows.Count).Interior.Color = vbRed         If .Parent.FilterMode Then .Parent.ShowAllData        .Range("E2").Clear    End With         Application.ScreenUpdating = TrueEnd Sub  ```
Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

5. Re: Comparing 2 Sheets in a Workbook

No vba.

1) Select A2:Dx, x is a row number that you want to apply.
2) Conditional Formatting Enter Formula :
=ABS(\$D2-\$C2)>=VLOOKUP(\$B2,Sheet1!\$A\$1:B\$100,2,FALSE)
3) Select cell format.

6. This is brilliant! Thanks guys! The only problem with it is that if no thresholds are exceeded, it colors all page red. (You can see what I mean by setting all thresholds in my example to 100)

I meant in Marc L example.

7. Re: Comparing 2 Sheets in a Workbook

My demo revamped :

PHP Code:
``` Sub Demo1r()     Dim A\$         A = Sheet1.Range("A2", Sheet1.[B1].End(xlDown)).Address(External:=True)    With Sheet2.[A1].CurrentRegion.Columns("A:D")        .Interior.ColorIndex = xlNone         Application.ScreenUpdating = False        .Range("E2").Formula = "=ABS(C2-D2)>VLOOKUP(B2," & A & ",2,FALSE)"        .AdvancedFilter xlFilterInPlace, .Range("E1:E2")         If Application.Subtotal(103, .Columns(1)) > 1 Then .Rows("2:" & .Rows.Count).Interior.Color = vbRed         If .Parent.FilterMode Then .Parent.ShowAllData        .Range("E2").Clear    End With         Application.ScreenUpdating = TrueEnd Sub  ```
Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

8. Re: Comparing 2 Sheets in a Workbook

Thank you so much! I''ll try it first thing Monday morning at work.

If it's not too much to ask, could you please explain why you used 103 here:
Application.Subtotal(103, .Columns(1)) > 1

Thank you soooooooooooo much!!!

Nevermind, sorry, Found the answer here if anyone is interested:

https://support.office.com/en-us/art...0-E478765B9939

9. Re: Comparing 2 Sheets in a Workbook

Yes that's it ! As an Excel worksheet function …

10. Re: Comparing 2 Sheets in a Workbook

Thank you so much! I learned more from this thread than from reading a book! You guys rock!!!!

11. Re: Comparing 2 Sheets in a Workbook

Many thanks for the rep's Lana !

12. Re: Comparing 2 Sheets in a Workbook

You are welcome and thanks for the rep.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1