Matches and differences between two lists

Hi,

I have my mind boggled on this one...

I am trying to compare matches and differences in two lists in order to pull statistics from them.

I have a production schedule I make at the start of the week (list 1) and compare that to what I actually ran at the end of the week (list 2). I would like to produce the following from these two lists;

- a list (list 3) that shows all the orders I didn't run in my original schedule
- a way of summing the number of matches (of work order #s) in the two lists (list 1 and list 2)
- and way of extracting the corresponding machine hours I did / did not run from the two lists

I have provided text boxes on the attached excel file to better explain my problem.

Reeeeeeaally hoping someone can help.

Melvin

Not sure exactly what you mean by "'Orders Not Run at End of 7-Day Window"

I interpret it as if order was run more than 7 days after the planned date...and used formula:

=IF($I12>$H12+7,F12,"") copied down and across the table... but got no data...

Formula in S13:

Please Login or Register  to view this content.

which matches items from List 1 to List 2...

In S14:

Please Login or Register  to view this content.

in S15:

Please Login or Register  to view this content.

Wow! That was quick - and everthing seemed to work great!

Apologies - my explanation on "Orders Not Run at End of 7-Day Window" wasn't too clear. Actually, what I'm after is a list of all the orders I planned to run (in list 1) that I didn't actually run (in list 2). In short, a list of all the orders in List 1 that aren't listed in List 2.

Can this be done and represented as a list without gaps in List 3?

If you can crack this, you will help alleviate A LOT of manual sorting.

Thanks,

In E12, enter formula:

=IF(ISNA(MATCH(A12,F:F,0)),MAX(E$11:E11)+1,"")

and copy it down. This counts the number of items from List 1 that are not in List 2.

You can change font colour to match background if you want to hide them.

Then in L12, enter formula:

=IF(ROWS($A$12:$A12)>MAX($E:$E),"",INDEX(A:A,MATCH(ROWS($A$1:$A1),$E:$E,0)))

and copy it down and across the table. You may have to format the date columns to preferred date format.