Order list of dates and adjacent values using formula

  • Re: Order list of dates and adjacent values using formula


    This works for everything apart from duplicated dates (put in C1 of your example sheet and drag down)


    =INDEX($B$1:$B$21,MATCH(LARGE($A$1:$A$21,ROW()),$A$1:$A$21,FALSE),1)


    the formula uses the row() to determine the large(x,1) formula, this will organise from most recent date. To organise in reverse replace large with small.
    I do not think that you can allow for multiple instances of the same date using a simple formula but someone else might have a better idea.

  • Re: Order list of dates and adjacent values using formula


    boydio,


    I've already cautioned you about thread titles. Read the forum rules and, going forward, be more precise when giving your thread a title.


    In C1: =RANK(A1,$A$1:$A$21)+ROW()/1000000


    In E1: =INDEX($A$1:$A$21,MATCH(LARGE($C$1:$C$21,ROW()),$C$1:$C$21,0))
    In F1: =INDEX($B$1:$B$21,MATCH(LARGE($C$1:$C$21,ROW()),$C$1:$C$21,0))


    This will order the dates in ascending order, along with the adjacent values.
    To order them in descending order used SMALL instead of Large.

  • Re: Order list of dates and adjacent values using formula


    When you say you need a formula I assume you mean a macro? The following macro will sort your list into date order.



    Open up Visual Basic Editor (on developer tab) and insert a module. Paste the code into the editor window then close the editor. To run the macro select Macros in the developer tab and select the sortData macro and run it.


    Hope this helps
    Anthony

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Order list of dates and adjacent values using formula


    Justin:
    Thank you so much, it worked for me.


    AAE:
    My sincere apologies, I just find it really difficult to find an appropriate title sometimes.
    Your method works too, thank you.


    I have attached another example this time with 2 lists.
    Could you include the new formula to combine the 2 lists into 1 list in date order.
    Thanks in advance

  • Re: Order list of dates and adjacent values using formula


    Hi,


    the sheet you posted appears to be identical? All you have to do though is use the index function twice so if you have the data you want to concatenate in B and C use:


    =INDEX($B$1:$B$21,MATCH(LARGE($A$1:$A$21,ROW()),$A$1:$A$21,FALSE),1) & " " & INDEX($C$1:$C$21,MATCH(LARGE($A$1:$A$21,ROW()),$A$1:$A$21,FALSE),1)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!