 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Excel Errors / March 2008Comparing lists
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 Can you upload a sample file & post a direct link to it here? You could use: http://www.freefilehosting.net/  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Please help. > I am trying to check a list of new part numbers against a list of old part [quoted text clipped - 7 lines] > > Thanks Thanks for the interest Max. Here is sample as requested. http://www.freefilehosting.net/download/3d12d Hope the link works ok. Tom > Can you upload a sample file & post a direct link to it here? > You could use: [quoted text clipped - 10 lines] > > > > 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 SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Thanks for the interest Max. > Here is sample as requested. 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 > Here's your sample, with the thoughts below implemented: > http://www.freefilehosting.net/download/3d1bk [quoted text clipped - 11 lines] > > Here is sample as requested. > 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 > Here's your sample, with the thoughts below implemented: > http://www.freefilehosting.net/download/3d1bk [quoted text clipped - 11 lines] > > Here is sample as requested. > http://www.freefilehosting.net/download/3d12d > 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. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Hi Max, > [quoted text clipped - 11 lines] > > 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? [quoted text clipped - 19 lines] > > > > Tom Welcome, Tom. Glad it helped. For info, a more suitable newsgroup to post such questions would be excel.worksheet.functions. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Thanks Max that has worked very well. It shall save a few hours of sifting > through numbers. [quoted text clipped - 4 lines] > > Thanks again for your help that formula was impressive! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.