FILTER function with replacing text

[UserI]

Hi. I have an Excel file with sales data, such as sales agent, location as city, product, quantity, customer, amount etc. With FILTER function I fetch to a new sheet the sales amount for all sales agents and certain cities across Europe. Is it possible to indicate within the formula that the city should be replaced with the country? I have a separate list with the corresponding countries for the cities. Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[UserI]

One more question: is it possible within the same formula to apply some custom sorting on the filtered sheet? Let's say France data to be displayed on top, then Italy, then Spain etc., assuming there is a hierarchy. Thank you.

 

[Fluff]

If you sort the City_country list in the order you want the countries to appear, you can use

=LET(f,FILTER(Data!A:P,(ISNUMBER(SEARCH("r",Data!D:D))*(Data!P:P>=5000))),x,XLOOKUP(INDEX(f,,4),City_Country!A2:A100,City_Country!B2:B100,"",0),s,SEQUENCE(,16),SORTBY(IF(s=4,x,INDEX(f,SEQUENCE(ROWS(f)),s)),MATCH(x,City_Country!B2:B100,0),1))

 

[UserI]

If you sort the City_country list in the order you want the countries to appear, you can use

=LET(f,FILTER(Data!A:P,(ISNUMBER(SEARCH("r",Data!D:D))*(Data!P:P>=5000))),x,XLOOKUP(INDEX(f,,4),City_Country!A2:A100,City_Country!B2:B100,"",0),s,SEQUENCE(,16),SORTBY(IF(s=4,x,INDEX(f,SEQUENCE(ROWS(f)),s)),MATCH(x,City_Country!B2:B100,0),1))

Thank you so much for the solution!

 

[Fluff]

My pleasure.

 

[UserI]

Hi. Need your help again. I have now an additional sorting to apply. Keeping the hierachy by country already established, need to see the order date in descending. Is it possible? Thank you so much!

 

[Fluff]

Try

=LET(f,FILTER(Data!A:P,(ISNUMBER(SEARCH("r",Data!D:D))*(Data!P:P>=5000))),x,XLOOKUP(INDEX(f,,4),City_Country!A2:A100,City_Country!B2:B100,"",0),s,SEQUENCE(,16),SORTBY(IF(s=4,x,INDEX(f,SEQUENCE(ROWS(f)),s)),MATCH(x,City_Country!B2:B100,0),1,INDEX(f,,10),-1))

 

[UserI]

Try

=LET(f,FILTER(Data!A:P,(ISNUMBER(SEARCH("r",Data!D:D))*(Data!P:P>=5000))),x,XLOOKUP(INDEX(f,,4),City_Country!A2:A100,City_Country!B2:B100,"",0),s,SEQUENCE(,16),SORTBY(IF(s=4,x,INDEX(f,SEQUENCE(ROWS(f)),s)),MATCH(x,City_Country!B2:B100,0),1,INDEX(f,,10),-1))

Perfect, thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.