Comparing lists

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Please help.
I am trying to check a list of new part numbers against a list of old part
numbers to see if they have been done before.
My spreadsheet has a serial number in column A and a part number in column
B. I have other columns but they are not important at the moment. I have
created a new sheet in the to paste the new list to be compared and I have
managed to get columns to count the instances of the new parts in the old
list but I would like to be able to get the sheet to put the latest serial
number along side the numbers to save me filtering the list manualy.

Thanks
 
Here's your sample, with the thoughts below implemented:
http://www.freefilehosting.net/download/3d1bk
Part_Numbers.xls

I'm not really sure what you're trying to do ..
Anyway, going by your original post,
In New List,
if you place in C2, and copy down: =IF(A2="","",COUNTIF(Parts!E:E,A2))
this would: >> .. count the instances of the new parts in the old list ..
Then, to extract the "CTO" for the part#, place in D2:
=IF(OR(C2=0,C2=""),"",INDEX(Parts!B:B,MATCH(A2&"",Parts!E:E,0)))
Copy D2 down

---
Tom said:
Thanks for the interest Max.
Here is sample as requested.
Click to expand...
http://www.freefilehosting.net/download/3d12d
 
Hi Max,

That works very well thankyou. Extracting the "cto" number saves me having
to manualy filter and search the old list.

The old list is updated daily as a record of the jobs that we do, it has
been running for 5 years and has over 6000 entries. Some part numbers are
repeated a few times.

Is it possible to add to the index cto formula so that it will return the
latest or greatest number?

Thanks

Tom
 
Hi Max,

That works very well thankyou. Extracting the "cto" number saves me having
to manualy filter and search the old list.

The old list is updated daily as a record of the jobs that we do, it has
been running for 5 years and has over 6000 entries. Some part numbers are
repeated a few times.

Is it possible to add to the index cto formula so that it will return the
latest or greatest number?

Thanks

Tom
 
Is it possible to add to the index cto formula so that it will return the
latest or greatest number?
Click to expand...

Put this instead into D2's formula bar, then "array-enter" the formula ie
press CTRL+SHIFT+ENTER [CSE] to confirm the formula (instead of just pressing
ENTER):
=IF(OR(C2=0,C2=""),"",INDEX(Parts!B$4:B$7000,MATCH(MAX(IF(A2&""=Parts!E$4:E$7000,ROW(Parts!E$4:E$7000))),IF(A2&""=Parts!E$4:E$7000,ROW(Parts!E$4:E$7000)),0)))
Copy D2 down. Adapt the ranges to suit.

---
 
Thanks Max that has worked very well. It shall save a few hours of sifting
through numbers.
The only problem I now have is that some of the "CTO" entries are alpha
numeric ie. 1345,1345A,1345B.
I think I willl change them to read 1345.1, 1345.2 etc instead and then I
shouldn't get the #na returned when the formula picks up the letters.

Thanks again for your help that formula was impressive!

Max said:
Is it possible to add to the index cto formula so that it will return the
latest or greatest number?
Click to expand...

Put this instead into D2's formula bar, then "array-enter" the formula ie
press CTRL+SHIFT+ENTER [CSE] to confirm the formula (instead of just pressing
ENTER):
=IF(OR(C2=0,C2=""),"",INDEX(Parts!B$4:B$7000,MATCH(MAX(IF(A2&""=Parts!E$4:E$7000,ROW(Parts!E$4:E$7000))),IF(A2&""=Parts!E$4:E$7000,ROW(Parts!E$4:E$7000)),0)))
Copy D2 down. Adapt the ranges to suit.

---
Tom said:
Hi Max,

That works very well thank you. Extracting the "cto" number saves me having
to manualy filter and search the old list.

The old list is updated daily as a record of the jobs that we do, it has
been running for 5 years and has over 6000 entries. Some part numbers are
repeated a few times.

Is it possible to add to the index cto formula so that it will return the
latest or greatest number?

Thanks

Tom
Click to expand...
Click to expand...
 
Welcome, Tom. Glad it helped.

For info, a more suitable newsgroup to post such questions would be
excel.worksheet.functions.
 
Back
Top