Sort data by categories between certain dates.

Frequent Contributor



I have a large amount of data which involves categories and transaction. I would like to find a formula which will automatically find the relevant information between certain date periods. I would then like it to be sorted by each category. I have attached an example.


Can someone please assist.


Kind regards,

10 Replies
HI, the attachment didn't come through. Have you used Power Query at all? Sounds ideal for that sort of thing


As @Wyn Hopkins  said, Power Query is a great option and can be easily refreshed to include new data.

If you have Office 365 then my preference will be to Dynamic Arrays SortBy function.

Good Luck

Nabil Mourad

HI@Wyn Hopkins 


Thanks for the message. I have reuattached the spreadsheet, so please let me know if any issues.


Unfortunately i have never used power query before, but keen to learn.


Thank you,



Thank you for the message.


As i have never used the DynamicArrays sort before, are you able to provide an example?


Many thanks,



Thank you for the message.


As i have never used the DynamicArrays sort before, are you able to provide an example?


Many thanks,




Actually a Pivot Table with a TimeLine slicer will give you what you need


see attached

Hi@Wyn Hopkins 


Looks great. Will work well for my needs.


Thanks again for your help, much appreciated.


Dynamic arrays have yet to be released (they are available within the Office 365 insider channel).

= SORT( FILTER(Table1, selected?, "Null"), 2 )

where selected? refers to the formula

= (Table1[Date]>=periodStart) * (Table1[Date]<=periodEnd)


Much as I like dynamic arrays, I suspect Power Query is better suited to your needs.


@Wyn Hopkins 

What are your criteria for deciding PQ or DA?  Factors that occur to me are:


Is the end user working with Office 365? If not, eliminate DA.

Problem size:

Up to 10 000 rows favour DA, up to 100 000 rows PQ, beyond PQ to data model and Power Pivot.

Use case:

External data imported periodically; think PQ and refresh

Assumptions changed to support 'what if' studies; suggests DA (no refresh required)

The above is simply initial speculation and in no way authoritative 


It is somewhat strange that the first dynamic array functions actually target gaps in list processing functionality (e.g. filtering and sorting) rather than pure array functionality (e.g. aggregate by row to get a column vector).




@Peter Bartholomew 


That list sounds sensible.  Dynamic Arrays will refresh immediately whereas with Power Query you have to manually refresh so there may be significant advantage there.  But I won't start using Dynamic Arrays until it gets added to the Semi Annual channel which may not be until mid next year or later.


Not really stress tested DAs on big data sets so don't know about row sizes.


Maybe a discussion for a different spot so we don't hammer @calof1 with this :)


Although finding a solution to an Excel problem is the main goal of being here, however, Being new to this community, I am much enjoying the discussions back and forth....  so on a second thought, I agree with @Wyn Hopkins  and @Peter Bartholomew  that for the issues of 

Availability of the Tool

Stability & Testing

Manual Refreshing

Then, Power Query would be a better option.

Thank you

Nabil Mourad


Related Conversations
Pulling data from one tab to another
krysphares in Excel on
6 Replies
Setting Date in Stored Procedure
Tim Hunter in SQL Server on
1 Replies
Date filtering of Bing Searches
PhilipE3 in Discussions on
2 Replies
Favorites Management
SmlBizAdmin in Discussions on
4 Replies
How often the sync is triggered?
HotCakeX in Discussions on
5 Replies