Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to select the date?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to select the date?
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 --- "Eric" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to select the date?
On Sun, 23 Nov 2008 06:11:00 -0800, Eric
wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to select the date?
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 "Max" wrote: 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 --- "Eric" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to select the date?
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 --- "Eric" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
select date in date range | Excel Worksheet Functions | |||
Date Select Macro | Excel Discussion (Misc queries) | |||
Select the most recent date | Excel Worksheet Functions | |||
Select criteria based on date | Excel Discussion (Misc queries) | |||
date criteria to select range | Excel Worksheet Functions |