Find occurance of a part number?
I have a computer sales program that will output an excel spredsheet that
contans all of my computer generated 10 digit part numbers. Part#'s are generated by options selected at time of order so there are literly thousands of different part#'s The part numbers are listed in two colums depending on if it was bought or sold. Each row transaction is loged with a date and time the order was generated. The row data and colum headers a Date - Time (am/pm) - sold - purchase - customer - vendor 5/20/2009 - 11:00 am - 10digits - 10digits - text - text 5/19/2009 - 3:09 pm - 10digits - 10digits - text - text Is there a function - formula or someway to search this sheet and find the (by Date/Time) first and last time a part number was purchsed and also the first and last time (by Date/Time) it was sold? I would like to pull this info into a seperate worksheet or tab. I would even be happy with somthing as simple as a way get a list that would just pull the entire row data into the worksheet for each of the different part#'s, first and last occurance. Thanks for any help. I am at a total loss! Premachine, (Mark) |
Find occurance of a part number?
If I understand what you want...
All formulas are array formulas**. H1 = part number to lookup First time sold: =MIN(IF(C2:C100=H1,A2:A100+B2:B100)) Last time sold: =MAX(IF(C2:C100=H1,A2:A100+B2:B100)) First time purchased: =MIN(IF(D2:D100=H1,A2:A100+B2:B100)) Last time purchased: =MAX(IF(C2:C100=H1,A2:A100+B2:B100)) Format as Date 3/14/01 1:30 PM ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Premachine" wrote in message ... I have a computer sales program that will output an excel spredsheet that contans all of my computer generated 10 digit part numbers. Part#'s are generated by options selected at time of order so there are literly thousands of different part#'s The part numbers are listed in two colums depending on if it was bought or sold. Each row transaction is loged with a date and time the order was generated. The row data and colum headers a Date - Time (am/pm) - sold - purchase - customer - vendor 5/20/2009 - 11:00 am - 10digits - 10digits - text - text 5/19/2009 - 3:09 pm - 10digits - 10digits - text - text Is there a function - formula or someway to search this sheet and find the (by Date/Time) first and last time a part number was purchsed and also the first and last time (by Date/Time) it was sold? I would like to pull this info into a seperate worksheet or tab. I would even be happy with somthing as simple as a way get a list that would just pull the entire row data into the worksheet for each of the different part#'s, first and last occurance. Thanks for any help. I am at a total loss! Premachine, (Mark) |
All times are GMT +1. The time now is 03:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com