Results 1 to 11 of 11
  1. #1
    New Lounger
    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 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 ...
    Thanks advanced
    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    Sep 2015
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is it possible or not? Please help me ..

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Vba or not?

  4. #4
    New Lounger
    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

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 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 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. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,828
    Thanks
    136
    Thanked 482 Times in 459 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 Ctrl-Shift-Enter.

    zeddy
    Last edited by zeddy; 2015-09-18 at 13:30.

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,828
    Thanks
    136
    Thanked 482 Times in 459 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)))
    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.
    Attached Files Attached Files
    Last edited by zeddy; 2015-09-18 at 17:27. Reason: typo

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Zeddy,

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

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,828
    Thanks
    136
    Thanked 482 Times in 459 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

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,828
    Thanks
    136
    Thanked 482 Times in 459 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 array-entered!

    zeddy
    Attached Files Attached Files

  11. #11
    New Lounger
    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..

Posting Permissions

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