# Thread: Sort dates in ascending order

1. ## 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 in
Code:
`range(C3:D3)`
Everything is ok

I need to have the results sorted according to the dates (in ascending order) using array formulas and without using helper columns ...

3. Vba or not?

4. 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

5. 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 Ctrl-Shift-Enter.

It should look like:

{=INDEX(Feed, MATCH(SMALL(COUNTIF(Feed, "<"&list), ROW(1:1)), COUNTIF(Feed, "<"&Feed), 0))}

Copy down to G16

HTH,
Maud

6. 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 Ctrl-Shift-Enter.

zeddy

7. 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)))```
instead of using the existing formula..
=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.

8. Zeddy,

Thanks for picking up on that. I was modifying an existing formula and missed it.

9. 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

10. 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 array-entered!

zeddy

11. Thanks a lot everybody
Thank you very much for this great solution..

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•