Sort as per earliest dates

[soumen21]

Hi,

I have a set of data as shown below


[TABLE="width: 275"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Value[/TD]
[TD]Category[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD] A[/TD]
[TD]3-Jun-19[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]B[/TD]
[TD]8-Aug-22[/TD]
[/TR]
[TR]
[TD]Blue [/TD]
[TD]C[/TD]
[TD]10-Jun-20[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]A[/TD]
[TD]12-Dec-19[/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]B[/TD]
[TD]12-Dec-21[/TD]
[/TR]
[TR]
[TD]Purple[/TD]
[TD]C[/TD]
[TD]5-Jul-23[/TD]
[/TR]
[TR]
[TD]Pink[/TD]
[TD]A[/TD]
[TD]30-Dec-20[/TD]
[/TR]
[TR]
[TD]Brown[/TD]
[TD]A[/TD]
[TD]21-Nov-20[/TD]
[/TR]
[TR]
[TD]Magenta[/TD]
[TD]B[/TD]
[TD]5-Mar-19[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]A[/TD]
[TD]24-Jul-23[/TD]
[/TR]
[TR]
[TD]Lime[/TD]
[TD]A[/TD]
[TD]13-Feb-28

[/TD]
[/TR]
</tbody>[/TABLE]

I want to sort it this way


[TABLE="width: 964"]
<colgroup><col span="2"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Enter Category[/TD]
[TD]<A>[/TD]
[TD="colspan: 2"]Before 2020[/TD]
[TD="colspan: 2"]Between 2020-2022[/TD]
[TD="colspan: 2"]After 2023[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Value[/TD]
[TD]Date[/TD]
[TD]Value[/TD]
[TD]Date[/TD]
[TD]Value[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Red[/TD]
[TD]3-Jun-19[/TD]
[TD]Brown[/TD]
[TD]21-Nov-20[/TD]
[TD]Orange[/TD]
[TD]24-Jul-23[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Green[/TD]
[TD]12-Dec-19[/TD]
[TD]Pink[/TD]
[TD]30-Dec-20[/TD]
[TD]Lime[/TD]
[TD]13-Feb-28[/TD]
[/TR]
</tbody>[/TABLE]

Any formulas to use for sorting this way. Please help.

Regards

 

[Misca]

Try something like:

=IFERROR(INDEX($A$1:$A$12,AGGREGATE(15,6,(ROW($A$1:$A$12)/(($C$1:$C$12>=$G$1)*($C$1:$C$12<$H$1))),ROWS($A$1:$A2)-ROW($A$1))),"")

The formula looks horrible but it's not that complicated when you understand what it's doing. The heart of the formula is the INDEX that returns the actual value from the given range. I have my lookup table in A1:C12 and I'm using helper cells in G1 and H1 to give me the min and max values for the dates in the given bin. I'm using two dates for each bin but you could use just one date for the first and last bins.

I'm using AGGREGATE to return the row numbers for the INDEX. The 15 stands for SMALL and 6 tells AGGREGATE to skip error values. The array for the AGGREGATE is row number divided by a bunch of trues and falses.

I could've used a double negative to turn the booleans to ones and zeroes but since I've got two booleans I can simply multiply them with each other. The first one asks is the date in row bigger than my min date for my bin and the next one asks are you smaller than my max date. AGGREGATE returns a bunch of row numbers and errors but since it's ignoring the errors I'm getting just the row numbers.

The last bit of my formula (the ROWS-ROW bit) is just returning the k for the SMALL in AGGREGATE (1,2,3 etc). The outer IFERROR is there only to hide the error values you'd get if you asked for the 4 th smallest row number when there's only 3 values that match my criteria. If you've got a really big data set that you're doing this you'd better replace the IFERROR with a "IF rownumber > COUNTIF matches then return blank, otherwise do the calculation" because that way you'd get a way with a lot less calculations (IFERROR calculates each cell and only hides the errors).

 

[soumen21]

Hi Misca, Thanks for your reply. Could you please attach the spreadsheet where you tried this formula it would be helpful.

 

[Misca]

Hope this works.

 

[soumen21]

Thanks a lot Misca. Appreciate your help.