Googled all over and haven't been able to find a solution- thanks in
advance for any help!

In Col A I have a list of items that are due for arrival; in Col B I
have the corresponding arrival dates. List must remain unsorted
(sorted would be too easy!)

Col A Col B

Monkey 12/1/07
Cat 1/15/08
Cow 11/21/07
Monkey 11/18/07
Peanut 1/30/08
Monkey 12/24/07
Cow 11/14/07

In Col C I need a formula to drag down that will give the earliest
date available, so for Monkey the date 11/18/07 would fill in for
every instance of Monkey; Cow would get 11/14/07.

Is it possible to set column D with a formula that would give the
next< possible available date? So for Monkey, the answer would be
12/1/07?

Thanks again!

You don't need any formulas. Put a header cell on top of both column. Then
setup a pivot with animal in the row area and min of date in the data area.
You should get:

Min of date
animal Total
Cat 1/15/2008
Cow 11/14/2007
Monkey 11/18/2007
Peanut 1/30/2008

for your sample data
--
Gary''s Student - gsnu2007a


" wrote:

Googled all over and haven't been able to find a solution- thanks in
advance for any help!

In Col A I have a list of items that are due for arrival; in Col B I
have the corresponding arrival dates. List must remain unsorted
(sorted would be too easy!)

Col A Col B

Monkey 12/1/07
Cat 1/15/08
Cow 11/21/07
Monkey 11/18/07
Peanut 1/30/08
Monkey 12/24/07
Cow 11/14/07

In Col C I need a formula to drag down that will give the earliest
date available, so for Monkey the date 11/18/07 would fill in for
every instance of Monkey; Cow would get 11/14/07.

Is it possible to set column D with a formula that would give the
next< possible available date? So for Monkey, the answer would be
12/1/07?

Thanks again!

Try in C1, array-entered with CTRL+SHIFT+ENTER:
=INDEX($B$1:$B$100,MATCH(SMALL(IF($A$1:$A$100=$A1, $B$1:$B$100),COLUMNS($A:A)),IF($A$1:$A$100=$A1,$B$ 1:$B$100),0))
Copy C1 to D1, fill down. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
oups.com...
Googled all over and haven't been able to find a solution- thanks in
advance for any help!

In Col A I have a list of items that are due for arrival; in Col B I
have the corresponding arrival dates. List must remain unsorted
(sorted would be too easy!)

Col A Col B

Monkey 12/1/07
Cat 1/15/08
Cow 11/21/07
Monkey 11/18/07
Peanut 1/30/08
Monkey 12/24/07
Cow 11/14/07

In Col C I need a formula to drag down that will give the earliest
date available, so for Monkey the date 11/18/07 would fill in for
every instance of Monkey; Cow would get 11/14/07.

Is it possible to set column D with a formula that would give the
next< possible available date? So for Monkey, the answer would be
12/1/07?

Thanks again!

Format cols C and D as dates
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

How about this array formula:

=MIN(IF(A$1:A$100=A1,B$1:B$100))

--
Gary''s Student - gsnu2007


"Max" wrote:

Try in C1, array-entered with CTRL+SHIFT+ENTER:
=INDEX($B$1:$B$100,MATCH(SMALL(IF($A$1:$A$100=$A1, $B$1:$B$100),COLUMNS($A:A)),IF($A$1:$A$100=$A1,$B$ 1:$B$100),0))
Copy C1 to D1, fill down. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
oups.com...
Googled all over and haven't been able to find a solution- thanks in
advance for any help!

In Col A I have a list of items that are due for arrival; in Col B I
have the corresponding arrival dates. List must remain unsorted
(sorted would be too easy!)

Col A Col B

Monkey 12/1/07
Cat 1/15/08
Cow 11/21/07
Monkey 11/18/07
Peanut 1/30/08
Monkey 12/24/07
Cow 11/14/07

In Col C I need a formula to drag down that will give the earliest
date available, so for Monkey the date 11/18/07 would fill in for
every instance of Monkey; Cow would get 11/14/07.

Is it possible to set column D with a formula that would give the
next< possible available date? So for Monkey, the answer would be
12/1/07?

Thanks again!

On Nov 6, 11:08 am, Gary''s Student
wrote:
How about this array formula:

=MIN(IF(A$1:A$100=A1,B$1:B$100))

--
Gary''s Student - gsnu2007

"Max" wrote:
Try in C1, array-entered with CTRL+SHIFT+ENTER:
=INDEX($B$1:$B$100,MATCH(SMALL(IF($A$1:$A$100=$A1, $B$1:$B$100),COLUMNS($A:A)),IF($A$1:$A$100=$A1,$B$ 1:$B$100),0))
Copy C1 to D1, fill down. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
roups.com...
Googled all over and haven't been able to find a solution- thanks in
advance for any help!

In Col A I have a list of items that are due for arrival; in Col B I
have the corresponding arrival dates. List must remain unsorted
(sorted would be too easy!)

Col A Col B

Monkey 12/1/07
Cat 1/15/08
Cow 11/21/07
Monkey 11/18/07
Peanut 1/30/08
Monkey 12/24/07
Cow 11/14/07

In Col C I need a formula to drag down that will give the earliest
date available, so for Monkey the date 11/18/07 would fill in for
every instance of Monkey; Cow would get 11/14/07.

Is it possible to set column D with a formula that would give the
next< possible available date? So for Monkey, the answer would be
12/1/07?

Thanks again!

Thanks everyone!!!

Welcome. Think my earlier suggestion was overdone. No need for the
INDEX/MATCH. Just the SMALL part would suffice here.

In C1, array-entered with CTRL+SHIFT+ENTER:
=SMALL(IF($A$1:$A$100=$A1,$B$1:$B$100),COLUMNS($A: A))
Format C1 as date. Copy C1 to D1, fill down. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote
Thanks everyone!!!

You didn't respond to your post from yesterday!

It's best to stick to a single thread concerning a single subject.

http://tinyurl.com/2xexre

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
wrote in message
oups.com...
On Nov 6, 11:08 am, Gary''s Student
wrote:
How about this array formula:

=MIN(IF(A$1:A$100=A1,B$1:B$100))

--
Gary''s Student - gsnu2007

"Max" wrote:
Try in C1, array-entered with CTRL+SHIFT+ENTER:
=INDEX($B$1:$B$100,MATCH(SMALL(IF($A$1:$A$100=$A1, $B$1:$B$100),COLUMNS($A:A)),IF($A$1:$A$100=$A1,$B$ 1:$B$100),0))
Copy C1 to D1, fill down. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
roups.com...
Googled all over and haven't been able to find a solution- thanks in
advance for any help!

In Col A I have a list of items that are due for arrival; in Col B I
have the corresponding arrival dates. List must remain unsorted
(sorted would be too easy!)

Col A Col B

Monkey 12/1/07
Cat 1/15/08
Cow 11/21/07
Monkey 11/18/07
Peanut 1/30/08
Monkey 12/24/07
Cow 11/14/07

In Col C I need a formula to drag down that will give the earliest
date available, so for Monkey the date 11/18/07 would fill in for
every instance of Monkey; Cow would get 11/14/07.

Is it possible to set column D with a formula that would give the
next< possible available date? So for Monkey, the answer would be
12/1/07?

Thanks again!

Thanks everyone!!!