Hi all,
I require a formula to put a list in date order (without using sort in menu).
Have attached an example.
Thanks in advance
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.
Code
Display MoreOption Explicit Sub sortData() Dim dateRange As Range Set dateRange = Range("A1:B" & Range("A65000").End(xlUp).Row) dateRange.Select ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A" & Range("A65000").End(xlUp).Row) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("A1:B" & Range("A65000").End(xlUp).Row) .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub
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 -
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)
-
Re: Order list of dates and adjacent values using formula
Sorry Justin.
My bad...have re-attached it now.
Thanks for all your help. -
Re: Order list of dates and adjacent values using formula
=index($b$1:$b$21,match(large($a$1:$a$21,row()),$a$1:$a$21,false),1) & " " & index($e$1:$e$21,match(large($d$1:$d$21,row()),$d$1:$d$21,false),1)
-
Re: Order list of dates and adjacent values using formula
Justin,
I have re-attached the list again to clearly show what I mean.
I require formulas for G2:H43
Thanks for all your help.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!