SOLVED
Home

Sort data by categories between certain dates.

%3CLINGO-SUB%20id%3D%22lingo-sub-716046%22%20slang%3D%22en-US%22%3ESort%20data%20by%20categories%20between%20certain%20dates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-716046%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20large%20amount%20of%20data%20which%20involves%20categories%20and%20transaction.%20I%20would%20like%20to%20find%20a%20formula%20which%20will%20automatically%20find%20the%20relevant%20information%20between%20certain%20date%20periods.%20I%20would%20then%20like%20it%20to%20be%20sorted%20by%20each%20category.%20I%20have%20attached%20an%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20please%20assist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-716046%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-716062%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20data%20by%20categories%20between%20certain%20dates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-716062%22%20slang%3D%22en-US%22%3EHI%2C%20the%20attachment%20didn't%20come%20through.%20Have%20you%20used%20Power%20Query%20at%20all%3F%20Sounds%20ideal%20for%20that%20sort%20of%20thing%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-716294%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20data%20by%20categories%20between%20certain%20dates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-716294%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%20said%2C%20Power%20Query%20is%20a%20great%20option%20and%20can%20be%20easily%20refreshed%20to%20include%20new%20data.%3C%2FP%3E%3CP%3EIf%20you%20have%20Office%20365%20then%20my%20preference%20will%20be%20to%20%3CSTRONG%3EDynamic%20Arrays%20SortBy%3C%2FSTRONG%3E%20function.%3C%2FP%3E%3CP%3EGood%20Luck%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-718580%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20data%20by%20categories%20between%20certain%20dates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718580%22%20slang%3D%22en-US%22%3E%3CP%3EHI%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20message.%20I%20have%20reuattached%20the%20spreadsheet%2C%20so%20please%20let%20me%20know%20if%20any%20issues.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%20i%20have%20never%20used%20power%20query%20before%2C%20but%20keen%20to%20learn.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-718588%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20data%20by%20categories%20between%20certain%20dates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718588%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20message.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20i%20have%20never%20used%20the%20DynamicArrays%20sort%20before%2C%20are%20you%20able%20to%20provide%20an%20example%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-718841%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20data%20by%20categories%20between%20certain%20dates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718841%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EActually%20a%20Pivot%20Table%20with%20a%20TimeLine%20slicer%20will%20give%20you%20what%20you%20need%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Esee%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-718590%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20data%20by%20categories%20between%20certain%20dates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718590%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20message.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20i%20have%20never%20used%20the%20DynamicArrays%20sort%20before%2C%20are%20you%20able%20to%20provide%20an%20example%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-718925%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20data%20by%20categories%20between%20certain%20dates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718925%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELooks%20great.%20Will%20work%20well%20for%20my%20needs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%20for%20your%20help%2C%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719256%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20data%20by%20categories%20between%20certain%20dates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719256%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDynamic%20arrays%20have%20yet%20to%20be%20released%20(they%20are%20available%20within%20the%20Office%20365%20insider%20channel).%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20SORT(%20FILTER(Table1%2C%20selected%3F%2C%20%22Null%22)%2C%202%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ewhere%20%3CSTRONG%3Eselected%3F%3C%2FSTRONG%3E%20refers%20to%20the%20formula%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20(Table1%5BDate%5D%26gt%3B%3DperiodStart)%20*%20(Table1%5BDate%5D%26lt%3B%3DperiodEnd)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EMuch%20as%20I%20like%20dynamic%20arrays%2C%20I%20suspect%20Power%20Query%20is%20better%20suited%20to%20your%20needs.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EWhat%20are%20your%20criteria%20for%20deciding%20PQ%20or%20DA%3F%26nbsp%3B%20Factors%20that%20occur%20to%20me%20are%3A%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3EAvailability%3A%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3EIs%20the%20end%20user%20working%20with%20Office%20365%3F%20If%20not%2C%20eliminate%20DA.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3EProblem%20size%3A%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3EUp%20to%2010%20000%20rows%20favour%20DA%2C%20up%20to%20100%20000%20rows%20PQ%2C%20beyond%20PQ%20to%20data%20model%20and%20Power%20Pivot.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3EUse%20case%3A%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3EExternal%20data%20imported%20periodically%3B%20think%20PQ%20and%20refresh%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EAssumptions%20changed%20to%20support%20'what%20if'%20studies%3B%20suggests%20DA%20(no%20refresh%20required)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CEM%3EThe%20above%20is%20simply%20initial%20speculation%20and%20in%20no%20way%20authoritative%3C%2FEM%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EIt%20is%20somewhat%20strange%20that%20the%20first%20dynamic%20array%20functions%20actually%20target%20gaps%20in%20list%20processing%20functionality%20(%3CEM%3Ee.g.%20filtering%20and%20sorting%3C%2FEM%3E)%20rather%20than%20pure%20array%20functionality%20(%3CEM%3Ee.g.%20aggregate%20by%20row%20to%20get%20a%20column%20vector%3C%2FEM%3E).%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719288%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20data%20by%20categories%20between%20certain%20dates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719288%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20list%20sounds%20sensible.%26nbsp%3B%20Dynamic%20Arrays%20will%20refresh%20immediately%20whereas%20with%20Power%20Query%20you%20have%20to%20manually%20refresh%20so%20there%20may%20be%20significant%20advantage%20there.%26nbsp%3B%20But%20I%20won't%20start%20using%20Dynamic%20Arrays%20until%20it%20gets%20added%20to%20the%20Semi%20Annual%20channel%20which%20may%20not%20be%20until%20mid%20next%20year%20or%20later.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20really%20stress%20tested%20DAs%20on%20big%20data%20sets%20so%20don't%20know%20about%20row%20sizes.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMaybe%20a%20discussion%20for%20a%20different%20spot%20so%20we%20don't%20hammer%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3Bwith%20this%20%3A)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719331%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20data%20by%20categories%20between%20certain%20dates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719331%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlthough%20finding%20a%20solution%20to%20an%20Excel%20problem%20is%20the%20main%20goal%20of%20being%20here%2C%20however%2C%20Being%20new%20to%20this%20community%2C%20I%20am%20much%20enjoying%20the%20discussions%20back%20and%20forth....%26nbsp%3B%20so%20on%20a%20second%20thought%2C%20I%20agree%20with%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%20and%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%20that%20for%20the%20issues%20of%26nbsp%3B%3C%2FP%3E%3CP%3EAvailability%20of%20the%20Tool%3C%2FP%3E%3CP%3EStability%20%26amp%3B%20Testing%3C%2FP%3E%3CP%3EManual%20Refreshing%3C%2FP%3E%3CP%3EThen%2C%20Power%20Query%20would%20be%20a%20better%20option.%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

Hi,

 

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

@calof1 

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,

Hi@nabilmourad 

 

Thank you for the message.

 

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

 

Many thanks,

Hi@nabilmourad 

 

Thank you for the message.

 

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

 

Many thanks,

Solution

@calof1 

 

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.

@calof1 

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:

Availability:

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 :)

@calof1 

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
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies