help with 2 workbook comparison?

[ajlfw]

So let me try to explain my problem. My company does a report each month for what we need to ship and it comes out in an excel document. Something like:

So it's my job to go thru and find out if everything is on schedule and if we need to hurry production of certain jobs. My problem arises when we do the report for the next month. Quite often, customers will change the delivery date, or quantity of the items they want, as well as new orders comming in. It becomes quite a chore to wade thru the whole report again to weed out the duplicates and only show me the new ones I need to be concerned about. I've tried using Vlookup and some of the other examples I've found online for comparing the differences between two Workbooks, and nothing seems to work. Mostly because i need to do it on a row by row basis I think. I have multiple deliveries of the same part and job numbers, and I don't have a way to uniquely identify each shipment. I even toyed around with trying to import the data to access thinking a database would maybe remove any duplicated information but I don't know it very well. I'd prefer to stay with excel if at all possible as that's the format in which I receive the report.

Changes are in Yellow. Aslo notice that some of the lines disappeared because they were shipped. The Green squares are where my comments were from the previous worksheet. If at all possible I would like it to pull all the data from the old workbook to the new workbook. Or, spit out a list of the changes from the old workbook to the new workbook and then I could just go manually make the changes to the old one where nessicary. Thanks for your help.

 

[Derek Brown]

You did not say which verion of Office is being used or where the data is coming from - if from a database then it should be possible for the extract to include a unique ID.

If you cannot uniquely identify each record, I don't think that you will be able to compare two lists.

 

[ajlfw]

we have both 2003 and 2007 office.

The information is comming from a 3rd party program that compiles the list in the excel format. Bottom line is, the way I receive the data isn't going to change (unfortunately) as the person who gives it to me is set in their ways and doesn't like change.

I do not know if there is a way to do it in excel. I am mostly looking for any kind of solution that would save me time, it would just be easier for me to do it in excel, since that's the way I receive the data.

 

[ajlfw]

I could use a little help w/ a solution please, even a non-excel related one. Thanks for your input

 

[Derek Brown]

If you cannot uniquely identify each record, I don't think that you will be able to compare two lists.

If Job, Customer, Customer_PO and Part_Number are always present could they be considered to be a unique 'key' if concatenated for comparison?

 

[ajlfw]

often times, customers will have the same Part # on multiple deliveries on the same PO. So the only thing that would change in that scenario is the delivery date. See how in my first example, Job #5556 has cheese for delivery twice.

 

[Derek Brown]

The information is comming from a 3rd party program that compiles the list in the excel format. Bottom line is, the way I receive the data isn't going to change (unfortunately) as the person who gives it to me is set in their ways and doesn't like change.

I know what I would say if you are the 'Customer', because the 'report' does not meet your needs .....

As I have already said, without a unique identifier it is not possible to adequately compare the two lists.

However, you may be able to go some way towards your goal by identifying those records that have not changed, so what is left has either changed or is a new record.

Here is an example that may give you some ideas.
First of all, to help show how it works I 'duplicated' one of the records by adding a second instance of:
8/13 5687 Candy 8
in the second table and also added the comment "Floor" to both records.

I set up each table on a separate worksheet and therefore the worksheet names (as will appear in the formulas) are Sheet1 and Sheet2.
Five column headers were added to each table:
Helper 1, Helper 2, Unique Key, Original Sequence, Match
These can be hidden later if required and could, if necessary, even be on a different worksheet (but the formulas would then become longer).

Add formulas on Sheet1 as follows:
I2: =A2&B2&C2&D2&E2&F2&G2&H2
J2: =COUNTIF($I$2:I2,I2)
K2: =I2&J2
M2: =IFERROR(MATCH(K2,Sheet2!$K$2:$K$10,0),"")
(Note that where I have used $K$10, it needs to be changed to reference the last row of data.) See below if you are doing this in Excel 2003.

Copy-down the formulas in I2 to M2 down to the last row of data.
In Column L just number the records 1, 2, 3 etc. but note that the '1' will actually be on Row 2.

Do exactly the same on Sheet2 but change the formula in column M to reference Sheet1.

Column M on Sheet1 now contains the row number for matching records on Sheet2 and vice versa - you can then add conditional formatting to apply a grey fill to the records that match, leaving the remaining ones more visible for checking.
I have used the formula in column I to concatenate all the other cells but of course you can have fewer if necssary.
The formula in column J shows how many instances of the 'unique key' exist (this is why I duplicated one of the records in the second table).

This only provides a work-around and is not the ideal solution. Also note that IFERROR will not work in Excel 2003 - for this you will need something like the following:

=IF(ISNA(MATCH(K2,Sheet2!$K$2:$K$10,0)),"",MATCH(K2,Sheet2!$K$2:$K$10,0))

Other ideas:
You could sort the tables for checking purposes and later use the 'Original Sequence' column, to return them to the original order.
You could add the relevant worksheet name to each table and then merge and sort the tables for easier visual checking, but do this on a 'copy' of the worksheets with formulas converted to values.