Previous Topic Next Topic

How to compare two Excel sheets with varying data

[Solved/Closed]
Report
Posts
6
Registration date
Monday September 7, 2015
Status
Member
Last seen
September 10, 2015
 -
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
 -
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.


Related:

6 replies

Answer 1 / 6
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
 484
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
5
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report
Posts
6
Registration date
Monday September 7, 2015
Status
Member
Last seen
September 10, 2015
 4
Dear Towra,

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
Report
Posts
6
Registration date
Monday September 7, 2015
Status
Member
Last seen
September 10, 2015
 4 >
Posts
6
Registration date
Monday September 7, 2015
Status
Member
Last seen
September 10, 2015
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.
Answer 2 / 6
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
 484
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
Report

Oh My GOD!

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
Report
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
 484
Wow, so much praise.

Thank you Ryan, you made my day!
Answer 3 / 6
Posts
6
Registration date
Monday September 7, 2015
Status
Member
Last seen
September 10, 2015
 4
Hello Everyone, Attached is a SS of the issue's I hope it assists your in helping me, Thank you In Advanced.
Ryan
Report
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
 484
One more request Ryan.

Like your screenshot above, could you post another pic showing how you foresee the end result?

Best regards,
Trowa
Answer 4 / 6
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
 484
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
Report
Posts
3
Registration date
Sunday November 22, 2015
Status
Member
Last seen
December 6, 2015
Hi Trowad,

At first, sincere apologies for replying so late. I was stuck with some issues and could not check out/reply on time.

Now, what should I tell you.... YOU ARE EXCEL GOD :) ..... of course it worked smoothly, without any glitch......

Only the columns which I wanted to copy/paste were missed out. Being from IT background ,I figured out that range was the issues

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

In this code I altered the range( N to R) as per my requirement and it worked smoothly with all columns copy/pasted.

Thanks a ton Trowad...... In future if I have any excel query, you will be my GURU to seek advice :)

Best,
robzzz
Report

I was actually trying to restrict the match to only one column & highlight that !
For Column A !! I see the code available highlights upto Column H !
Report
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
 484
Hi Venki,

You mean like this?: 
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


Best regards,
Trowa
Answer 5 / 6
Posts
6
Registration date
Monday September 7, 2015
Status
Member
Last seen
September 10, 2015
 4
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
Answer 6 / 6
Posts
3
Registration date
Sunday November 22, 2015
Status
Member
Last seen
December 6, 2015


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
Report
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
 484
OK rob, I'll check it out.
Ask a question

On the same themes