×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Comparing two spread sheets in the same format

Comparing two spread sheets in the same format

Comparing two spread sheets in the same format

(OP)
Over a month ago I was given a BOM in the form of a excel spread sheet and was just given an updated version of it.  A great deal of the assemblies have expanded throughout the BOM.  Is there anyway I can compare the two spread sheets so that I can easily find the differences?

RE: Comparing two spread sheets in the same format

(OP)
Ok, after pondering through every post in this forum I came across this post... http://www.eng-tips.com/viewthread.cfm?qid=120407&page=5 and once again Melone was a big help.

Although that was a step in the right direction, is there any way I can compare the two spread sheets and highlight the differences.  

I guess what I would want is a for it to take part numbers from the "new" BOM spread sheet, search for their equal on the "old" BOM, if it finds the p/n on the "old" BOM it would then compare the part's attributes and highlight diffences on the "new" BOM.  If it didn't find the part number on the "old" BOM, it would highlight the entire line on the "new" BOM.

Oh yes, BOM=Bill of Materials or in otherwords.. a Part List incase someone doesn't know what it stood for.

Thanks all

RE: Comparing two spread sheets in the same format

Tricky. If it was just a one-off I would first sort them, then write them out as csv files. Then I would use the free package winmerge to compare them.

Now, that does seem a crazy way round, given excel's database-like capabilities. Using Match and so on you should be able to do what you want in VBA, but I think you'd spend several hours writing and testing the thing .



Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Comparing two spread sheets in the same format

And, it would be slower than mud.  Probably much easier to port to Word and compare in Word, although, that's not without its share of annoyances.

TTFN



RE: Comparing two spread sheets in the same format

Use conditional formatting to highlight the differences....

1) Put your data is columns A and C.

2)  Paste the following formula into column B
  =IF(AND(ISERROR(MATCH(A4,$C$4:$C$172,0)),NOT(ISBLANK(A4))),"Diff",IF(A4="","","Same"))

This will compare the value in cell A4 to cell range C4-C172.  If your list is longer, simply increase the range.  Remember to use the $C$x format.  This will allow you to autofill to the bottom of your list while keeping the range constant.

3) Paste  the following formula into column D
  =IF(AND(ISERROR(MATCH(C4,$A$4:$A$172,0)),NOT(ISBLANK(C4))),"Diff",IF(C4="","","Same"))

Same as above.  Actually, in my spreadsheet, I have some VBA to find the end of each of the lists, and automatically size the range, but let's just start with this for now.

4) Use conditional formatting to highlight the cells in column A & C based on the values of column B & D.

The whole beauty of this approach is that the list do NOT have to be sorted.  Let us know if you have any questions.

RE: Comparing two spread sheets in the same format

In most BOM, there is a part number. If this is the case, try this:

1) BOM 1, hightlight yellow.
2) BOM 2, highlight green.
3) Put both lists onto single page.
4) Sort
5) Using conditional formatting, in the left most column, if the part number of Row 1 = Row 2, highlight in red.
6) Go back and scan for red highlights.

RE: Comparing two spread sheets in the same format

Air code, but I've used it many times before...just for those little BOMS you don't want to look at colored cells to choose:

Sub Comparison()
Dim u as long
Dim v as long
Dim x as long
Dim y as long
Dim yhi as long
Dim yLo as long
Dim z as long
Dim aItemNo1() As Long

u = 9629 'Number of rows in BOM1
v = 6084 'Number of rows in BOM2
reDim aItemNo1(u) As Long

'Loads the part numbers from first BOM into memory
For y = 1 To u
    aItemNo1(y) = Workbooks("BOM1.xls").Sheets(1).Cells(y, 1).Value
Next y

yHi = u
yLo = 1
For x = 1 To v
    'Gets second BOM part number one at a time
    ItemNo2 = Workbooks("BOM2.xls").Sheets(1).Cells(x, 1).Value
    'Does the searching to find if its there
    Do
        y =  (ylo + yhi) \ 2
        z =  aItemNo1(y) - ItemNo2
        Select Case Z
            Case 0
                exit do
            Case Is < 0
                yLo = y + 1
            Case else
                yHi = y - 1
        End Select
        if yLo > yhi then
            y = 0
            exit do
        end if
    Loop
    
    If y = 0 Then
       'If you cant find the second BOM P/N in the first BOM, write the PN and comment to other excel file.
       Workbooks("Addedparts.xls").Sheets(1).Cells(i, 1) = ItemNo2
       Workbooks("Addedparts.xls").Sheets(1).Cells(i, 2) = "No Match"
       i = i + 1

    Else
        For Y = Y To 1 Step -1
            if aItemNo1(y) <> ItemNo2 then exit for
        Next
        For y = y + 1 To U
            if aItemNo1(y) <> ItemNo2 then exit for
            h = h + 1
        Next
    End If
Next x
End Sub

NOTE: Iif you reverse the two bom names and run again you can get the parts REMOVED from BOM...someday I'll automate that.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources