How to compare two Excel sheets with varying dataSolved/Closed

Question

Hello, 
Before I start let me just say Thanks in advance.

So I am looking for an ongoing updated answer. Every 3 months we receive a new price list from our suppliers. Column A is an item number that stays true over every update, but the list price Column D for each row may or may not change from tri month to month. I there a way to setup a master workbook, so it will read the data for each Item number across sheet 1 & 2 and tell me if it has changed. There are roughly 1100 products listed every 3 months, and a simple way of seeing if data in column D matches column A over the 2 sheets would make this process of finding changes and not having to search every product every 3 months a lot easier. 
I'll include an example below. 

Sheet 1- VALID010415_030315 
Item Number| Description | List Price | 
#30 | Product | 0.50| 
Sheet 2- VALID010915_030915 
Item Number | Description | List Price | 
#30 | Product | 0.55| 

Col A is Item Number 
Col B is description 
Col D is list price. 

I only need to know if the Col D has changed since previous sheet, but if new products are added or some removed, if would be helpful to have something alerting me of these occurrences. 


Example If 
Sheet 1- VALID010415_030315 
A4| Item Number| Description| List Price| 
A35| #30 | Product | 0.50| 

But on Sheet 2 
Sheet 2- VALID010915_030915 
A4| Item Number | Description | List Price | 
A49 | #30 | Product | 0.55| 
Because some products have changed by way of addition or subtraction I kinda need to know so I dont take the cells of Col D and their values and adjust all prices incorrectly. 

Is this making sense to anyone. I'm struggling to Explain? 
I am very appreciative of anyone who can assist with my dilemma in advance, And I look forward to any ongoing feedback you may provide.


Configuration: Windows 7 / Chrome 47.0.2498.0

TrowaD · Accepted Answer

Hi Ryan,

So sheet1 has the old prices and sheet2 has the updated prices. Then why is sheet1 still relevant?

A solution could be to create a new sheet (Master sheet). Copy the products from both sheet to this master sheet. Then remove the duplicates and use VLOOKUP to retrieve the prices. Put the prices from sheet1 in a different column then the prices from sheet2. Then use conditional format to highlight differences in prices.

Sheet1:
Item | Price
#1 | 0.50
#2 | 0.75

Sheet2:
#1 | 0.55
#2 | 0.75

Master Sheet:
#1 | 0.50 | 0.55 <-- Here is a difference so CF will highlight this row.
#2 | 0.75 | 0.75 <-- No difference here.

Is this a solution that could work for you?
Can you make this work?

Best regards,
Trowa

BWIRyan · Answer

Hello Everyone, Attached is a SS of the issue's I hope it assists your in helping me, Thank you In Advanced.
Ryan

BWIRyan · Answer

Hi TrowaD

Ok so in an ideal world to save time. I would hope to be able to achieve something like this.# Please note this is manually achieved, for I don't know how to do using excel.


Really Appreciate your time in helping with this matter. Many Thanks
Regards Ryan

TrowaD · Answer

Hi Ryan,

In the code below I assumed that you used row 1 for the header in both sheets.

Here is the code:
Sub RunMe()
Dim sValue As Range
Dim lRow1, lRow2 As Integer

lRow2 = Sheets("Sheet2").Range("A1").End(xlDown).Row
Sheets("Sheet1").Select
lRow1 = Range("A1").End(xlDown).Row

For Each cell In Range("A2:A" & lRow1)
    Set sValue = Sheets("Sheet2").Range("A2:A" & lRow2).Find(what:=cell.Value)
    If sValue Is Nothing Then GoTo NextCell
    If cell.Offset(0, 3).Value = sValue.Offset(0, 3).Value Then
        Sheets("Sheet1").Select
        Range("H" & cell.Row).Value = "No"
        With Sheets("Sheet1").Range(Cells(cell.Row, "A"), Cells(cell.Row, "H")).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.399975585192419
            .PatternTintAndShade = 0
        End With
        
        Sheets("Sheet2").Select
        Range("H" & sValue.Row).Value = "No"
        With Sheets("sheet2").Range(Cells(sValue.Row, "A"), Cells(sValue.Row, "H")).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.399975585192419
            .PatternTintAndShade = 0
        End With

Else:
        Sheets("Sheet1").Select
        Range("H" & cell.Row).Value = "Yes"
        With Sheets("Sheet1").Range(Cells(cell.Row, "A"), Cells(cell.Row, "H")).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        
        Sheets("Sheet2").Select
        Range("H" & sValue.Row).Value = "Yes"
        With Sheets("sheet2").Range(Cells(sValue.Row, "A"), Cells(sValue.Row, "H")).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
NextCell:
Next cell
End Sub

In case you don't know .....
How to implement and run a code:

- From Excel hit Alt + F11 to open the "Microsoft Visual Basic" window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro's.
- Double-click the macro you wish to run.
NOTE: macro's cannot be reversed using the blue arrows. Always make sure you save your file before running a code, so you can reopen your file if something unforeseen happens or you want to go back to the situation before the code was run.
 

Do let me know how this works out for you.


Best regards,
Trowa

robzzz0489 · Answer

Hi #TrowaD,

I have uploaded my query in form of a snapshot. Please help me !!! 
M in need of desperate help ...... hope you reply asap

TrowaD · Answer

Hi Rob, Try the following code: Sub RunMe() Dim lRow As Integer Dim rFound As Boolean lRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row Sheets("Sheet2").Select For Each cell In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row) rFound = False x = 1 Do x = x + 1 If cell & cell.Offset(0, 1) & cell.Offset(0, 2) & cell.Offset(0, 3) & cell.Offset(0, 4) & cell.Offset(0, 5) _ = Sheets("Sheet1").Cells(x, 1) & Sheets("Sheet1").Cells(x, 2) & Sheets("Sheet1").Cells(x, 3) & _ Sheets("Sheet1").Cells(x, 4) & Sheets("Sheet1").Cells(x, 5) & Sheets("Sheet1").Cells(x, 6) Then rFound = True Sheets("Sheet1").Select Sheets("Sheet1").Range(Cells(x, "N"), Cells(x, "R")).Copy Sheets("Sheet2").Select Sheets("Sheet2").Range(Cells(cell.Row, "N"), Cells(cell.Row, "R")).PasteSpecial Application.CutCopyMode = False If cell.Offset(0, 6) <> Sheets("Sheet1").Cells(x, 7) Then cell.Offset(0, 6).Interior.Color = 255 Sheets("Sheet1").Cells(x, 7).Interior.Color = 5287936 End If If cell.Offset(0, 7) <> Sheets("Sheet1").Cells(x, 8) Then cell.Offset(0, 7).Interior.Color = 255 Sheets("Sheet1").Cells(x, 8).Interior.Color = 5287936 End If End If Loop Until x = lRow If rFound = False Then Range(Cells(cell.Row, 1), Cells(cell.Row, 8)).Interior.Color = 255 Next cell End Sub Let me know how this works out for you. Best regards, Trowa

How to compare two Excel sheets with varying data

6 replies

Similar discussions

Subscribe To Our Newsletter!