Find occurance of a part number?

[Premachine]

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 are:

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)

 

[T. Valko]

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(D2100=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.

 

[T. Valko]

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(D2100=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.