+ Reply to Thread
Results 1 to 12 of 12

Comparing 2 Sheets in a Workbook

  1. #1
    Registered User
    Join Date
    02-12-2010
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    27

    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. #2
    Valued Forum Contributor kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016/Work 2013 Pro Plus
    Posts
    1,348

    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.

    Go Advanced -> Manage Attachments -> Upload

  3. #3
    Registered User
    Join Date
    02-12-2010
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: Comparing 2 Sheets in a Workbook

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

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    2,516

    Cool Hi ! Try this ‼


    According to your attachment as a beginner starter :

    PHP Code: 
    Sub Demo1()
         
    Dim 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," ",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 True
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon Add Reputation !

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    12,719

    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.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-12-2010
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    27
    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.
    Last edited by Lana74; 07-06-2018 at 02:57 PM.

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    2,516

    Re: Comparing 2 Sheets in a Workbook


    My demo revamped :

    PHP Code: 
    Sub Demo1r()
         
    Dim 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," ",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 True
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon Add Reputation !
    Last edited by Marc L; 07-06-2018 at 07:56 PM. Reason: optimization …

  8. #8
    Registered User
    Join Date
    02-12-2010
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    27

    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
    Last edited by Lana74; 07-06-2018 at 10:39 PM.

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    2,516

    Re: Comparing 2 Sheets in a Workbook


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

  10. #10
    Registered User
    Join Date
    02-12-2010
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    27

    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. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    2,516

    Re: Comparing 2 Sheets in a Workbook


    Many thanks for the rep's Lana !

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    12,719

    Re: Comparing 2 Sheets in a Workbook

    You are welcome and thanks for the rep.

    Please mark the thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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