Comparing lists

[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

 

[Max]

Can you upload a sample file & post a direct link to it here?
You could use:
http://www.freefilehosting.net/

 

[Tom]

Thanks for the interest Max.

Here is sample as requested. http://www.freefilehosting.net/download/3d12d

Hope the link works ok.

Tom

 

[Max]

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

Thanks for the interest Max.
Here is sample as requested.

http://www.freefilehosting.net/download/3d12d

 

[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

 

[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

 

[Max]

Is it possible to add to the index cto formula so that it will return the

latest or greatest number?

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]

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!

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

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.

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

 

[Max]

Welcome, Tom. Glad it helped.

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