compare 2 lists

  • Thread starter Thread starter Tonso
  • Start date Start date
T

Tonso

I import into Excel 2 reports, One report is today's machine status,
the other report is yesterdays machine status. Each report has 2
columns. One column is the machine number. The other is the lot number
running on the machine. If the lot number changes during the day, that
machine number will show up 2 times, once with the old lot number, and
once with the new lot number. The only way to tell which is the new
lot number, is to look at the old lot on the machine yesterday. I
currently do this manually. Is there a function or functions I can use
to automate this? Below is an example.

Todays Data Yesterdays Data
Machine Lot Machine Lot
1 223 1 223
2 223 2 212
3 510 3 510
3 426 4 331
4 331 5 477
5 477 5 833

In this example, there was a change in the lot on machine 3 today,
since machine 3 shows up 2x under Todays Data. The only way to
determine the new lot is to see that yesterday the old lot was 510, so
the new lot must be 426. Is there a function or functions i can use so
i can list each machine number [in this case 1 - 5], and have it look
at the 2 reports and determine what is currently on each machine. I
have tried several schemes but with no success.

Thanks

Tonso
 
Hi,

Suppose your first list in in A1:B6 and your second list is in E1:F6 then a
cell on row 1 enter the following formula and copy it down six rows.

=IF(IF(COUNTIF($A$1:$A$6,A1)>1,1,0)>0,SUMPRODUCT((A1=$E$1:$E$6)*(B1<>$F$1:$F$6)),0)

It will mark the new number in lot 3 with a value of 1.

If this helps, click the Yes button.
 
Back
Top