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)
|