|
Array Lookup to Find Closest Date and Next Closest Date
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! |
Array Lookup to Find Closest Date and Next Closest Date
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! |
Array Lookup to Find Closest Date and Next Closest Date
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! |
Array Lookup to Find Closest Date and Next Closest Date
|
Array Lookup to Find Closest Date and Next Closest Date
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! |
Array Lookup to Find Closest Date and Next Closest Date
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!!! |
Array Lookup to Find Closest Date and Next Closest Date
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!!! |
Array Lookup to Find Closest Date and Next Closest Date
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!!! |
| All times are GMT +1. The time now is 03:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com