How to compare two Excel sheets with varying data [Solved/Closed]
BWIRyan6
Posts Monday September 7, 2015Registration date
September 10, 2015
Last seen
-
Sep 7, 2015 at 09:22 PM - Latest reply: TrowaD2391
Posts Sunday September 12, 2010Registration dateModeratorStatus
July 12, 2018
Last seen
- Feb 18, 2016 at 11:10 AM
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.
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.
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?
You asked "So sheet1 has the old prices and sheet2 has the updated prices. Then why is sheet1 still relevant?"
It's relavant because if the value in the Column A on sheet 1 matches Sheet 2, Then I need to see if the valve in Column D has changed or stay the same.
We are finding not all products are changing in price. I cant just take the new spreadsheet and upload it into our CRM/Products table, I Have to enter it manually, Thus why i'm trying to compare the data between old and new, So as to make my import across policy a little easier, God Help me if they have all changed..
I will try what you have suggested and let you know, if your equation solves my riddle. Thanking you again. Ryan
BWIRyan6
Posts Monday September 7, 2015Registration date
September 10, 2015
Last seen
>
BWIRyan6
Posts Monday September 7, 2015Registration date
September 10, 2015
Last seen -Sep 8, 2015 at 08:03 PM
Dear Towra,
I don't believe this can work. Because The supplier add and remove products from month to month. So if I "Put the prices from sheet1 in a different column then the prices from sheet2. " Unless I sit there and manually copy each line, ensuring its Col A data matches, I can't just copy the Col. without first knowing if The data matches. I'll upload part of the document in bow below so u can see what I mean.
Thanking you again for taking time to assist me.
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.
You are an Excel GOD!!!!!!!!
If only you knew how much time this has saved me. Thankyou, Thankyou, Thankyou!!!!!
It works a treat!. Thats amazing..... I am keeping you as the almighty Guru of excel that I am now labeling you in my contact list!
So much appreciation!
Kind Regards
Ryan
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
Sub RunMe()
Dim uValue As Range
Sheets("Sheet1").Select
For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Set uValue = Sheets("Sheet2").Columns("A:A").Find(cell.Value)
If uValue Is Nothing Then cell.Interior.Color = 255
Next cell
End Sub
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
has helped 1740 users this month
Thank you for your prompt response.
You asked "So sheet1 has the old prices and sheet2 has the updated prices. Then why is sheet1 still relevant?"
It's relavant because if the value in the Column A on sheet 1 matches Sheet 2, Then I need to see if the valve in Column D has changed or stay the same.
We are finding not all products are changing in price. I cant just take the new spreadsheet and upload it into our CRM/Products table, I Have to enter it manually, Thus why i'm trying to compare the data between old and new, So as to make my import across policy a little easier, God Help me if they have all changed..
I will try what you have suggested and let you know, if your equation solves my riddle. Thanking you again. Ryan
I don't believe this can work. Because The supplier add and remove products from month to month. So if I "Put the prices from sheet1 in a different column then the prices from sheet2. " Unless I sit there and manually copy each line, ensuring its Col A data matches, I can't just copy the Col. without first knowing if The data matches. I'll upload part of the document in bow below so u can see what I mean.
Thanking you again for taking time to assist me.