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
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
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
TTFN
RE: Comparing two spread sheets in the same format
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
Cheers,
Joerd
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
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
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.