CODEWITHSUNDEEP
excelsortingarray-formulas
Erich
Erich

Reputation: 2626

sorting excel data formulaicly

with my organization's group policy settings forcing users to explicitly click to enable macros in office files, i'd like a workaround to using vba to achieve the same result (and without having to click anything as an added bonus). pivot tables are also out of the question.

i have a sheet with locations as column headings (A1:E1), and events as row headings (A2:A50). the remaining cells (B2:E50) are populated with dates representing when the next event at each location will be held. not all locations serve each event, so "N/A"s are possible. events may be held at the same location on the same day.

Event data:

my goal is to offer a secondary view of the data showing the current and next month's upcoming events (as row entries) beneath each location (column heading). this is (relatively) simple enough with an array formula copied over and down.

formula: =IFERROR(INDEX(Master!$A$6:$A$15,SMALL(IF(Master!B$6:B$15<>"N/A",IF((Master!B$6:B$15<DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))*(Master!B$6:B$15>=DATE(YEAR(TODAY()),MONTH(TODAY()),1)),ROW(Master!B$6:B$15)-5)),ROWS(A$3:A3))),"")

Output: output

however, the events are listed as they appear in the master sheet, not chronologically as i'd prefer. i added a date column next to each event, but this is not sortable, as the array formula keeps all the content dynamic.

desired output excerpt:

Tampa   
19-Jan-15   Intermediate I
24-Jan-15   Introduction
26-Jan-15   Beginner I

Upvotes: 0

Views: 277

Answers (1)

barry houdini
barry houdini

Reputation: 46411

In Upcoming worksheet it's easier if January in A1 is actually a date, the 1st of the relevant month, so in this case 1/1/2015.

You can custom format A1, if required, as "mmmm" to show just January or "mmmm-yy" to show January-15

Now use this formula in A3 to get the dates in order for the month and city in question (works whether you have duplicates or not):

=IFERROR(SMALL(IF(Master!B$6:B$15>=$A$1,IF(Master!B$6:B$15<=EOMONTH($A$1,0),Master!B$6:B$15)),ROWS(A$3:A3)),"")

confirmed with CTRL+SHIFT+ENTER and copied down column

Note: you don't have to explicitly exclude "N/A" text values in the formula - these will be ignored anyway

Now in B3 copied down you can use this non-array formula if you don't ever have duplicate dates for a specific city:

=IF(A3="","",INDEX(Master!$A$6:$A$15,MATCH(A3,Master!B$6:B$15,0)))

If you might have duplicate dates use this "array formula" instead for the description

=IF(A3="","",INDEX(Master!$A$6:$A$15,SMALL(IF(A3=Master!B$6:B$15,ROW(Master!$A$6:$A$15)-ROW(Master!$A$6)+1),COUNTIF(A$3:A3,A3))))

Upvotes: 1

Related Questions