Find the three nearest dates

[magdiego]

I have a table that contains names in b3:b19, and corresponding dates in
c3:c19. These dates are calculated, and change on a quarterly basis, and
there are often duplicate dates. The table is sorted by the names in column
B.

I want to retrieve the names in column B that have the next three upcoming
dates. These may be 3 different dates, or have 2 or 3 duplicates.

I can find the next 3 dates, using an array formula:

=SMALL(C3:C19,{1;2;3})

But that just returns the dates, not the name associated with the date.

Any suggestions?

TIA,

Mary

 

[Dave]

Hi,
This may be too simple, but what about temporarily sorting the data by the
date column. That would give you the three names at the top of the names
list. Then sort again by name to restore the data as it was.
Otherwise:
If your three cells that display the upcoming dates are in C24:C26, then in
B24 enter:
=INDEX($B$3:$B$19,MATCH(C24,$C$3:$C$19,0))
and copy down.

Regards - Dave.

 

[magdiego]

Thanks, Dave, but I'm trying to put this pull this data into another file,
and would rather not sort the data.

As far as the formula you supplied, if I'm reading it right, it assumes that
I know what the next 3 dates are, but I don't. I just need the the next 3,
whatever they are.

Thanks, though, for your reply.

Mary

 

[Dave]

Sorry, I thought you knew the next three dates using =SMALL(C3:C19,{1;2;3}).
In this case, array entered into C24:C26
Regards - Dave

 

[magdiego]

I'm sorry, you were right! I wasn't thinking, I can use the array formula to
get the next three dates. But my problem is that the dates may repeat. For
example, the next 3 items on my list have the due dates of 6/21, 6/28 and
6/28. So I get the 2 names fine, but instead of getting the third name, I
get the second name again.

I'm confusing myself. Here's a simplified version:

Col A Col B
Anne 7/11
Barb 7/18
Carl 6/28
Doug 7/4
Enid 6/21
Fred 8/30
Greg 6/28

I need to get a list that says "Enid: 6/21, Carl: 6/28, Greg: 6:28".

(Not necessarily in that format, of course!)

When I used your formula, I get "Enid: 6/21, Carl: 6/28, Carl: 6:28".

Hope this is more clear, and I do thank you!

 

[Dave]

Ah yes, I see that. Not sure how to proceed at present. With so many posts to
this thread, you may need to post again in a new one.
Regards - Dave.

 

[Dave]

Hi, I've been thinking - yeah, I know...

In each of three cells, enter:
=INDEX($B$3:$B$19,MATCH(SMALL($C$3:$C$19,1),$C$3:$C$19,0))
=INDEX($B$3:$B$19,MATCH(SMALL($C$3:$C$19,2),$C$3:$C$19,0))
=INDEX($B$3:$B$19,MATCH(SMALL($C$3:$C$19,3),$C$3:$C$19,0))

It seemed to work when I tested it.

Regards - Dave.

 

[T. Valko]

Try this array formula** :

A2:A8 = Name
B2:B8 = Date

Assume you want the list of names to start in cell D1.

Enter this array formula** in D1:

=IF(ROWS(D$11)<=COUNTIF(Date,"<="&SMALL(Date,3)),INDEX(Name,SMALL(IF(Date<=SMALL(Date,3),ROW(Name)-MIN(ROW(Name))+1),ROWS(D$11))),"")

This will extract the names in the order in which they appear in A2:A8. If
you want them to be extracted chronologically then a different approach will
be needed.

Copy down until you get blanks. Then you can use a VLOOKUP to get the dates
for the names.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

Won't work when there are duplicates that fall within SMALL(rng,{1,2,3})

 

[Dave]

Oh, yeah. So much for thinking, and so much for my testing!
Dave.

 

[magdiego]

This works - THANKS!

Mary

 

[T. Valko]

You're welocme. Thanks for the feedback!