How do I compare 2 excel spreadsheets?

[dgfullin]

I have 2 bill of materials for a product. I want to know the differences
between the 2. I want to know what was added, what was removed, if any
quantities changed.

 

[Eric]

dgfullin,

Depending on how big/complicated it is, you could just put a formula in one
spreadsheet that subtracts off the value in the same cell in the other
worksheet. For example, if you want to compare D10 in the other workbook
from the value in D10 in the current one, enter the following formula in E10
(or any other free cell):

='[Book2.xls]Sheet1'!D10 - D10

Then copy it down if you want to find the difference for a column. If you
want to compare the whole spreadsheet, make a new workbook and put the
following into A1:

='[Book2.xls]Sheet1'!A1 = '[Book1.xls]Sheet1'!A1

Then copy it over the range that there are values in the workbooks. It
will return TRUE when the cells are the same and FALSE when they're not.

(Obviously, you need to replace "Book1" and "Book2" with the actual names of
your files.

Eric

 

[dgfullin]

I tried this, but the way our BOMs are created, the columns match up, but
then they sort by part number, so none of my row cells match up. Any other
suggestions?
Thanks,
df

dgfullin,

Depending on how big/complicated it is, you could just put a formula in one
spreadsheet that subtracts off the value in the same cell in the other
worksheet. For example, if you want to compare D10 in the other workbook
from the value in D10 in the current one, enter the following formula in E10
(or any other free cell):

='[Book2.xls]Sheet1'!D10 - D10

Then copy it down if you want to find the difference for a column. If you
want to compare the whole spreadsheet, make a new workbook and put the
following into A1:

='[Book2.xls]Sheet1'!A1 = '[Book1.xls]Sheet1'!A1

Then copy it over the range that there are values in the workbooks. It
will return TRUE when the cells are the same and FALSE when they're not.

(Obviously, you need to replace "Book1" and "Book2" with the actual names of
your files.

Eric

I have 2 bill of materials for a product. I want to know the differences
between the 2. I want to know what was added, what was removed, if any
quantities changed.

 

[Dave Peterson]

Your part numbers are unique in each worksheet?

If yes, maybe you could use =vlookup() to retrieve the values from the second
worksheet and put them in helper columns to the right of your original data.

Say column A has the part number.
columns B:E has the data
then add F:I to return those values from the other worksheet

Then use J:M to compare the values:

=b2=f2
(and drag across and then drag it down.)

==========
If you could add part numbers in the second workbook, then this won't work. You
won't have a part number on the first worksheet to match on the second.

If that's the case, I'd get a single list of all the part numbers.

Insert a new worksheet and copy the part numbers from worksheet 1 to A1 of that
new worksheet. Then copy the part numbers from worksheet 2 at the bottom of
that list in column A.

Include just one header in A1.

Then use techniques at Debra Dalgleish's site to get just a single list of part
numbers:
http://www.contextures.com/xladvfilter01.html#FilterUR

Delete column A.

Then use =vlookup() to return the values from worksheet 1 and from worksheet 2
(in columns B:E and F:I like above).

Then compare the returned values.

=============
Another way that may work....

Save both worksheets as a .CSV (comma separated values).

Then use MSWord to compare these text files to see changes.

I tried this, but the way our BOMs are created, the columns match up, but
then they sort by part number, so none of my row cells match up. Any other
suggestions?
Thanks,
df

dgfullin,

Depending on how big/complicated it is, you could just put a formula in one
spreadsheet that subtracts off the value in the same cell in the other
worksheet. For example, if you want to compare D10 in the other workbook
from the value in D10 in the current one, enter the following formula in E10
(or any other free cell):

='[Book2.xls]Sheet1'!D10 - D10

Then copy it down if you want to find the difference for a column. If you
want to compare the whole spreadsheet, make a new workbook and put the
following into A1:

='[Book2.xls]Sheet1'!A1 = '[Book1.xls]Sheet1'!A1

Then copy it over the range that there are values in the workbooks. It
will return TRUE when the cells are the same and FALSE when they're not.

(Obviously, you need to replace "Book1" and "Book2" with the actual names of
your files.

Eric

I have 2 bill of materials for a product. I want to know the differences
between the 2. I want to know what was added, what was removed, if any
quantities changed.