Sort range by column when first row is a month abbreviation
A little trick to sort a range of data when the header record is a month abbreviation. Any better approaches?
=LET(months,TEXT(DATE(1,ROW(A1:A12),1),"mmm"), data,A1:F4, DROP(SORT(VSTACK(MATCH(TAKE(data,1),months,0),data),1,1,1),1)) Here's how the trick works. We add a new record to the top of data that is a numerical month number corresponding to the month abbreviation of "dec", "nov", "apr", etc. To do this, you first need a list of all months which is created by:
TEXT(DATE(1,ROW(A1:A12),1),"mmm")
This says use the date() function to generate 12 dates in the year 1 for the first of each month and display them using the text() function with a parm of "mmm" which gives us the "jan", "feb", "mar", etc. list. This saves you from having to define a custom list in the excel options for use in the sort() function.
Then we use the match() function to bang the first row of data against this month list. So, "nov", "feb", "dec",... gets translated to 11, 2, 12,... etc. We temporarily add these month numbers as a new first row of the data range with (say this is our newdata):
VSTACK(MATCH(TAKE(data,1),months,0),data)
Then we use sort() to sort the whole thing by columns with the first row being the sort index (the month numbers). Then we have to drop that first temporary row to get back to our original data.
SORT(newdata,1,1,1)
This is easily adopted to sort a range by rows with the first column holding the month abbreviations.
[link] [comments]
Want to read more?
Check out the full article on the original site