How to select the date?

E

Eric

Does anyone have any suggestions on how select the date?
There is a list of date under column A, and numbers under column B
02-Jan-08 1
06-Jan-08 2
06-Jan-08 0
12-Jan-08 1
16-Jan-08 1
20-Jan-08 3
21-Jan-08 4
23-Jan-08 4
24-Jan-08 3
30-Jan-08 2
01-Feb-08 3
03-Feb-08 3
06-Feb-08 3
07-Feb-08 2

I would like to select the date with higher numbers, the higher number and
smaller number in date always select firstly
It should show the result under D column, as show below
21-Jan-08
23-Jan-08
20-Jan-08
24-Jan-08
30-Jan-08
01-Feb-08
03-Feb-08
06-Feb-08
07-Feb-08
02-Jan-08
12-Jan-08
16-Jan-08
06-Jan-08

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric
 
M

Max

Read it that you're asking to auto-sort the dates in col A
by the numbers in col B (in descending order)

In C1: =IF(B1="","",B1-ROW()/10^10)
In D1: =INDEX(A:A,MATCH(LARGE(C:C,ROWS($1:1)),C:C,0))
Select C1:D1, copy down. Format col D as dates. Minimize/hide col C. Col D
returns the required auto-sorted dates from col A. Any dates with tied
numbers in col B (ties) will be returned in the same relative order that they
appear within the source.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
 
L

Lars-Åke Aspelin

Does anyone have any suggestions on how select the date?
There is a list of date under column A, and numbers under column B
02-Jan-08 1
06-Jan-08 2
06-Jan-08 0
12-Jan-08 1
16-Jan-08 1
20-Jan-08 3
21-Jan-08 4
23-Jan-08 4
24-Jan-08 3
30-Jan-08 2
01-Feb-08 3
03-Feb-08 3
06-Feb-08 3
07-Feb-08 2

I would like to select the date with higher numbers, the higher number and
smaller number in date always select firstly
It should show the result under D column, as show below
21-Jan-08
23-Jan-08
20-Jan-08
24-Jan-08
30-Jan-08
01-Feb-08
03-Feb-08
06-Feb-08
07-Feb-08
02-Jan-08
12-Jan-08
16-Jan-08
06-Jan-08

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

I think that 30-jan-08 has been misplaced in your example output.

Try this formula in cell C1:

=INDEX(A$1:A$14,MATCH(SMALL((-100000*(B$1:B$14)+(A$1:A$14)),ROW()),-100000*(B$1:B$14)+(A$1:A$14),0))

Note! This is an array formula that has to be entered by
SHIFT+CTRL+ENTER rather than just ENTER.

Change the 14 on all places to fit your size of data
(number of dates/numbers)

Hope this helps / Lars-Åke
 
E

Eric

Thank you very much for suggestions
Would it be possible to select the 3 dates for Jan only in H1,I1,J1 based on
the same conditions? In this case, it should return
21-Jan-08 in cell H1,
23-Jan-08 in cell I1,
Blank in cell J1

Would it be possible to select the 3 date for Feb only in H2,I2,J2 based on
the same conditions? In this case, it should return
01-Feb-08 in cell H2
03-Feb-08 in cell I2
06-Feb-08 in cell J2

.... for Mar, Apr, ... Dec

Do you have any suggestions?
Thank you very much for any suggestions
Eric
 
M

Max

Eric,
Do you always "reward" responders who answer your orig. posts with yet other
questions? Take a breather or two from all this asking and receiving. Try
reciprocating by answering questions posed by others. Apply what you have
learnt to help others in need. Its meaningful to give, to receive.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top