Comparing Spreadsheets

**Nilla3321** · 06-16-2015, 11:12 AM

I'm using Excel 2003 and need to compare 2 worksheets to one another to find the errors in our system. I've uploaded a small version of this to dropbox.
https://www.dropbox.com/s/l3q9px9gs81x37j/Test.xls?dl=0

I actually need to do a few things to this sheet. Catalog is my master sheet, System is the one I need to find the errors in so...

In System sheet
Find Part #
If Part # is located, check values in B-F to make sure they are the same - if not, highlight the offending cell.
Then grab the price from column G and add it to column G in the Catalog sheet
If Part # is not located - highlight part number

Now I've tried some conditional formatting, I copied and pasted my list of part numbers into the same sheet and was able to accomplish the last portion of my goal (finding part #'s that are in the System but not the Catalog)
=COUNTIF($A:$A, C1)

However when I attempted to branch that out and find mismatching descriptions (B-F) my brain exploded and I can't even really tell what I'm trying to do...it did something, but this is why I'm pretty sure this might require actual code. For this one I also put all my data into 1 sheet - I don't know how to tell excel to reference a different worksheet.
=ISERROR(MATCH($H12&I12&J12&K12&L12&M12, $A$2:$A$14&$B$2:$B$14&$C$2:$C$14&$D$2:$D$14&$E$2:$E$14&$F$2:$F$14,0))

Hopefully I've explained this in a way that makes sense, it's so hard to get my brain to wrap around it so I figured it was worth a shot asking here! I also included a 3rd sheet in the dropbox file with a manual version of what I'm after. Thanks so much!

**p24leclerc** · 06-16-2015, 11:59 AM

this formula in a conditional formatting works well but you have some data in your catalog that creates problems.

=B2<>VLOOKUP($A2,Cat!$A:$G,COLUMN(B2),FALSE)

Some numbers are entered as text and it creates issues with the conditional formatting.
Can you have this changed?

**Nilla3321** · 06-16-2015, 12:11 PM

Hm, maybe that's why I'm having so much trouble with it. The fractions have been trouble from the get-go, sometimes they want to change to dates, sometimes they insist on being decimals. I keep trying to format the cells but it doesn't seem to take, or I'll click into the cell and then it decides to chose the random format it desires. I'm assuming the little green triangle in the corner means it's acting like a number?

I did try to toss the VLOOKUP into the System sheet but it tells me "You may not use references to other worksheets or workbooks for conditional formatting criteria". Might have to tell the boss that this request is over my head XD

**p24leclerc** · 06-16-2015, 02:10 PM

this small modification seems to do the trick:

Public Sub Check_Cat()
Dim WS_Cat As Worksheet, WS_SYS As Worksheet
Dim C_ell As Range, F_ound As Range
'
Set WS_Cat = Sheets("Catalog")
Set WS_SYS = Sheets("System")
'
WS_SYS.Select
For Each C_ell In Range("A2", Cells(Rows.Count, 1).End(xlUp))
    Set F_ound = WS_Cat.Range("A:A").Find(C_ell, , , xlWhole)
    If Not F_ound Is Nothing Then
        'Cut Dia.
        If C_ell.Offset(0, 1).Text <> F_ound.Offset(0, 1).Text Then
            C_ell.Offset(0, 1).Interior.ColorIndex = 3
        End If
        'Shank Dia.
        If C_ell.Offset(0, 2).Text <> F_ound.Offset(0, 2).Text Then
            C_ell.Offset(0, 2).Interior.ColorIndex = 3
        End If
        'Loc.
        If C_ell.Offset(0, 3).Text <> F_ound.Offset(0, 3).Text Then
            C_ell.Offset(0, 3).Interior.ColorIndex = 3
        End If
        'OAL
        If C_ell.Offset(0, 4).Text <> F_ound.Offset(0, 4).Text Then
            C_ell.Offset(0, 4).Interior.ColorIndex = 3
        End If
        'Reach
        If C_ell.Offset(0, 5).Text <> F_ound.Offset(0, 5).Text Then
            C_ell.Offset(0, 5).Interior.ColorIndex = 3
        End If
        'Price is transfered to Catalog sheet
        F_ound.Offset(0, 6) = C_ell.Offset(0, 6)
    Else
        C_ell.Interior.ColorIndex = 6
    End If
Next
End Sub

**Nilla3321** · 06-16-2015, 02:19 PM

! That sure does seem to do the trick, thank you thank you! I'll have to study that code so I can try to modify it next time I'm asked for something like this. Going to give it a shot on 200 or so part #'s and then see what happens when I plug it into the 14k list, lol.

**p24leclerc** · 06-16-2015, 02:08 PM

you can try this macro but the same problem exist with some of your data:

Public Sub Check_Cat()
Dim WS_Cat As Worksheet, WS_SYS As Worksheet
Dim C_ell As Range, F_ound As Range
'
Set WS_Cat = Sheets("Catalog")
Set WS_SYS = Sheets("System")
'
WS_SYS.Select
For Each C_ell In Range("A2", Cells(Rows.Count, 1).End(xlUp))
    Set F_ound = WS_Cat.Range("A:A").Find(C_ell, , , xlWhole)
    If Not F_ound Is Nothing Then
        'Cut Dia.
        If C_ell.Offset(0, 1) <> F_ound.Offset(0, 1) Then
            C_ell.Offset(0, 1).Interior.ColorIndex = 3
        End If
        'Shank Dia.
        If C_ell.Offset(0, 2) <> F_ound.Offset(0, 2) Then
            C_ell.Offset(0, 2).Interior.ColorIndex = 3
        End If
        'Loc.
        If C_ell.Offset(0, 3) <> F_ound.Offset(0, 3) Then
            C_ell.Offset(0, 3).Interior.ColorIndex = 3
        End If
        'OAL
        If C_ell.Offset(0, 4) <> F_ound.Offset(0, 4) Then
            C_ell.Offset(0, 4).Interior.ColorIndex = 3
        End If
        'Reach
        If C_ell.Offset(0, 5) <> F_ound.Offset(0, 5) Then
            C_ell.Offset(0, 5).Interior.ColorIndex = 3
        End If
        'Price is transfered to Catalog sheet
        F_ound.Offset(0, 6) = C_ell.Offset(0, 6)
    Else
        C_ell.Interior.ColorIndex = 6
    End If
Next
End Sub

Comparing Spreadsheets

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Comparing Spreadsheets

Re: Comparing Spreadsheets

Re: Comparing Spreadsheets

Re: Comparing Spreadsheets

Re: Comparing Spreadsheets

Re: Comparing Spreadsheets

Thread Information

Users Browsing this Thread

Similar Threads

Comparing two spreadsheets

Comparing Spreadsheets

Comparing 2 Spreadsheets

[SOLVED] Comparing two spreadsheets

Comparing two spreadsheets

Bookmarks

Bookmarks

Posting Permissions