Results 1 to 11 of 11
Thread: Sort dates in ascending order

20150915, 12:52 #1
 Join Date
 Sep 2015
 Posts
 10
 Thanks
 0
 Thanked 0 Times in 0 Posts
Sort dates in ascending order
Hello everyone
In my attachment I have two sheets one for data named "INT" and the other sheet is for results named "FEED"
In FEED sheet I have array formulas that extract the data between two dates inCode:range(C3:D3)
I need to have the results sorted according to the dates (in ascending order) using array formulas and without using helper columns ...
Thanks advanced

20150916, 11:11 #2
 Join Date
 Sep 2015
 Posts
 10
 Thanks
 0
 Thanked 0 Times in 0 Posts
Is it possible or not? Please help me ..

20150917, 05:43 #3
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,919
 Thanks
 152
 Thanked 746 Times in 678 Posts
Vba or not?

20150917, 06:57 #4
 Join Date
 Sep 2015
 Posts
 10
 Thanks
 0
 Thanked 0 Times in 0 Posts
Not vba just array formulas ..
The array formulas are already existing but I got the results only as they are
I need to get the results sorted by editing the array formulas

20150917, 19:14 #5
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,919
 Thanks
 152
 Thanked 746 Times in 678 Posts
YasserKhalil,
Create a named range "Feed" having the range C7 to C16. In G7, enter the following array formula:
=INDEX(Feed, MATCH(SMALL(COUNTIF(Feed, "<"&list), ROW(1:1)), COUNTIF(Feed, "<"&Feed), 0)) then CtrlShiftEnter.
It should look like:
{=INDEX(Feed, MATCH(SMALL(COUNTIF(Feed, "<"&list), ROW(1:1)), COUNTIF(Feed, "<"&Feed), 0))}
Copy down to G16
HTH,
Maud

20150918, 03:28 #6
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,396
 Thanks
 164
 Thanked 634 Times in 602 Posts
Hi Maud
..I think there is a typo in your formula.
I think you meant to use:
=INDEX(Feed, MATCH(SMALL(COUNTIF(Feed, "<"&Feed), ROW(1:1)), COUNTIF(Feed, "<"&Feed), 0)) then CtrlShiftEnter.
zeddyLast edited by zeddy; 20150918 at 12:30.

20150918, 12:30 #7
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,396
 Thanks
 164
 Thanked 634 Times in 602 Posts
Hi Maud
I assume the poster actually wants to replace the existing formulas in the Date column [C] with a new formula.
To do this:
create the following names:
Code:Name Refers to: blockClients =INT!$D$5:$D$27 blockDates =INT!$C$5:$C$27 blockTotals =INT!$E$5:$E$27 date1 =FEED!$C$3 date2 =FEED!$D$3 dateRange =(date1<=blockDates)*(date2>=blockDates)*blockDates excludedCount =SUM(NOT((date1<=blockDates)*(date2>=blockDates)))
=IF(ROWS($C$7:$C7)>dateCount,"",INDEX(INT!$C$5:$C$ 27,SMALL(IF(($C$3<=blockDates)*($D$3>=blockDates), ROW(blockDates)4),ROWS($C$7:$C7))))
..you can now use this new simpler array formula to return the 'sorted' dates
=IFERROR(SMALL(dateRange,ROW(1:1)+excludedCount)," ")
..use [Ctrl][Shift][Enter] to enter this array formula, and copy down as required.
You can replace the existing formulas in column [C].However, you will also need updated formulas for the Clients and Total columns.
(I have left this for others to have a go)
see attached file.Last edited by zeddy; 20150918 at 16:27. Reason: typo

20150918, 16:11 #8
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,919
 Thanks
 152
 Thanked 746 Times in 678 Posts
Zeddy,
Thanks for picking up on that. I was modifying an existing formula and missed it.

20150918, 16:31 #9
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,396
 Thanks
 164
 Thanked 634 Times in 602 Posts
Hi Maud
..any suggestions for replacement formulas for corresponding columns Clients and Total as per my post#7 file? I would be very interested in solutions.
zeddy

20150919, 03:57 #10
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,396
 Thanks
 164
 Thanked 634 Times in 602 Posts
OK, here is the solution to the posters request.
The Dates are sorted (in ascending order) using array formulas and without using helper columns.
New array formulas in columns [D] and [E] are used to ensure the corresponding Clients and Total columns are 'synchronised' with the newly sorted dates.
If there are multiple entries for the same date (for example, 02/02/2015 in the posted example file), then the Client and Total will be as per the existing order as they appear on the [INT] sheet.
(So, in the example posted, Client 3 comes before Client 1 for the date 02/02/2015 as this is their order on sheet [INT]
To test my posted file, change the start date in cell [c3] on sheet [FEED] to 01/01/2015 to see results.
You can also make data changes to sheet [INT]
An interesting challenge to do this without vba and without helper columns.
My solution makes use of named ranges.
NOTE:
In Excel, all Named ranges are really 'named formulas' (thats why in the Define Name dialog box, the Refers to: field always starts with an equal sign  it's because it's a formula!!)
And just for good measure, all named formulas are treated as arrayentered!
zeddy

20150929, 16:24 #11
 Join Date
 Sep 2015
 Posts
 10
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks a lot everybody
Thank you very much for this great solution..