Using one formula to spill customer across 7 columns

[bearcub]

I have a report that I would like to create where I want to use the customers by region.

I could copy the filter function to all the columns but I was wondering if there was a better way to do this?

This is the formula that I came up with

SORT(
UNIQUE(
FILTER(CommissionTemplate!X:X, (CommissionTemplate!AH:AH=B2)*(CommissionTemplate!Z:Z=G6))))

Where the commission template is the source data, B2 is the revenue type & G6 is the country (APAC - which has 3 customers listed from the Commission Template sheet in the snapshot below):

Is there to add something to this formula that will spill the orders for all these regions or do I have to reuse the filter function for each column. Would this be were byRows or HStack be incorporated into the formula?

 

[etaf]

you could cover in the filter range the colums , X:AZ or however many columns have the results you want
and then use
choosecols() to select what columns you want to display
in this case X will be 1
so something like

choosecols(
SORT(
UNIQUE(
FILTER(CommissionTemplate!X:X, (CommissionTemplate!AH:AH=B2)*(CommissionTemplate!Z:Z=G6)))) ,
1,3, 10)
that will display col1 1 = X col 3 = Z , col 10 = AH

 

[RoryA]

If I understand correctly, something like this:

=IFERROR(DROP(REDUCE("",G6:O6,LAMBDA(s,c,HSTACK(s,SORT(
UNIQUE(
FILTER(CommissionTemplate!X:X, (CommissionTemplate!AH:AH=B2)*(CommissionTemplate!Z:Z=c))))))),,1),"")

 

[bearcub]

If I understand correctly, something like this:

=IFERROR(DROP(REDUCE("",G6:O6,LAMBDA(s,c,HSTACK(s,SORT(
UNIQUE(
FILTER(CommissionTemplate!X:X, (CommissionTemplate!AH:AH=B2)*(CommissionTemplate!Z:Z=c))))))),,1),"")

Hi Rory,

Thank you for the formula - I really like it.

I think the formula needs, though, to be tweaked slightly because the formula is return nothing when I enter it into my speadsheet:

I replaced "'CommissionTemplate!Z:Z=c" with "'CommissionTemplate!Z:Z=G6"and I get the following results:

Looking at G6 spills the results for the APAC across all the regions instead of return the new customers for each particular region.

I don't understand how the Lamba function works but it looks like the something in the section that was added the formula - DROP(REDUCE("",G6:O6,LAMBDA(s,c,HSTACK" (the "c' argument) is not returning the new customers from the source column.

The formula should work - thank you for this - but it needs to be tweaked slightly - I wish I knew how it worked so I could do the tweaking as needed.

 

[RoryA]

Oh, that would happen if one of the header values doesn't return any data. We can amend the FILTER to return an empty string rather than an error:

=IFERROR(DROP(REDUCE("",G6:O6,LAMBDA(s,c,HSTACK(s,SORT(
UNIQUE(
FILTER(CommissionTemplate!X:X, (CommissionTemplate!AH:AH=B2)*(CommissionTemplate!Z:Z=c),"")))))),,1),"")

 

[bearcub]

Worked like a charm:

Yes, a couple of regions didn't book anything in Feb or March. This has been remedied in April

Thank you for your help, love that formula. I found a 2 hour video by Girvin where he explains the Lambda function. I plan on listening to it today in between work tasks.

 

[Fluff]

In future please mark the post that contains the solution, rather than your post saying it works. Thanks

 

[bearcub]

Sorry, I thought I had marked it as complete.

 

[Fluff]

You did, but you marked post#6 not post#5 which is the post with the answer. You have now marked post#8 which does not contain a solution so I have changed it back to post#5

 

[bearcub]

Thank you for letting me know. I will do that in the future