Comparing two spread sheets in the same format

[JRW261]

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?

 

[JRW261]

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

 

[GregLocock]

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.

 

[IRstuff]

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

 

[melone]

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.

 

[joerd]

I downloaded a utility some time ago, probably from Chip Pearson's site

http://www.cpearson.com/Excel.htm.

He has a lot of good stuff there. The link is

http://www.cpearson.com/Zips/Compare.ZIP

Cheers,
Joerd

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

 

[Ashereng]

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.

 

[alexit]

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 = 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 - 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 <> ItemNo2 then exit for
Next
For y = y + 1 To U
if aItemNo1 <> 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.