Combining dynamic array functions

[Quadhurst]

I am amazed at the lack of internet help on this subject. I have searched high and low for detailed pieces about the syntax of combining more than one dynamic array functions, specifically for my needs, the SORT and FILTER functions and the SORTBY and FILTER functions.

I need to be able to process a table of data and both sort it by a number of columns but also filter either multiple values from just one column or values in multiple columns.

Can anyone help me learn how to do this? I understand the basic syntax of the SORT, SORTBY and FILTER functions; it's the combining of them that is eluding me and there's just almost nothing out there to help.

I am attaching a sample file that I've been experimenting with.

Many thanks.



Andrew

[Glenn Kennedy]

It's not very clear what you want to do... but here are a couple of things to start you off:

=SORT(UNIQUE(FILTER(Table1[Product],Table1[Class of product]="Food")))

=SUMIF(Table1[Product],H3#,Table1[Cost])

[Fluff13]

Somewhat more involved
=SORTBY(FILTER(FILTER(Table1,Table1[Class of product]="Alcohol"),COUNTIF(H2:J2,Table1[#Headers])),FILTER(Table1[Variance],Table1[Class of product]="Alcohol"),-1)

[Quadhurst]

Many thanks for your swift reply.

In answer to your question: it's not really what I want to do with the data - it was just sample cell entries I created; it's knowing the syntax of combining the two functions - how they are combined together. Once I know how to put it all together I am sure I'll be fine. It's just the complete absence of help on the internet which frustrated and surprised me: I thought there'd be a massive amount of stuff since dynamic array functions are relatively new.

Regards.



Andrew

[Limor_OP]

don't quite understand what you actually want, but maybe this can help

https://www.youtube.com/watch?v=Onudkw9DMlU

[Quadhurst]

Thank you for your response. I'm sorry that I'm not making myself clear. All I want to know is how you actually combine the two functions when you are building your formula. I know the syntax of FILTER; I know the syntax of SORT and SORTBY but there's nothing I've found that tells you how you build a formula combining the two [SORT/FILTER or SORTBY/FILTER].

Regards.



Andrew

[Fluff13]

Have you taken a look at the examples that Glenn & I supplied?

[Quadhurst]

Yes, thank you - much appreciated. I see that you include a COUNTIF function in the formula. The same goes for using the UNIQUE function.

It's probably better if I gave an example of the sort of thing I'd like to be able to do. I'd like to, for example, filter out 'Beverages' and 'Alcohol (so that's filtering two products in one column) and then sort by date and price. Alternatively, I'd like to filter out, say, 'Meat' in one column and cost under £10 in another and sort just by budget variance. I hope that makes things a little clearer.

I just want a template (effectively) to be able to create those sorts of formulae.

Regards.




Andrew

[Fluff13]

Do you have the LET function

[Fluff13]

How about

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

The first one can be simplified if you have the LET function.

[Quadhurst]

Many thanks. Yes, I do have the LET function but haven't used it or have the first clue what it does. What you have posted above, however, is going to be really useful since it will enable me to try to work out how the syntax works with combining the two functions: that's all I wanted to know. If. however, this LET function can help, too, I'd be very interested to know how it works.

Regards.



Andrew

[Fluff13]

The LET formula enables you to store information in variables so that you only need to calculate things once. This is the same as the Sortby formula

Please Login or Register  to view this content.

[Quadhurst]

Unfortunately I don't have the LET function at work. Presumably this is not yet implemented for all 365 users, just those on the inside rtacdk.

[Fluff13]

AFAIK all users on the monthly channel have it, not sure about the semi annual channel.