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.

 

[gordsky]

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.

what code are you currently using and where do you store the list of countries/cities

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some sample data of all relevant data, including the formula you are using.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[UserI]

what code are you currently using and where do you store the list of countries/cities

Hi, Here is a sample data.
Thank you.
Deleted

 

[UserI]

https://file.io/Ude0Ef1EHM4n

 

[Fluff]

That website is blocked for me, as it's been reported for Phishing attacks.

 

[UserI]

Sorry, hope this will be OK.

Sales data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Sales Agent ID	Sales Agent Name	Product	Location	Customer	Customer ID	Price	Order number	Quantity	Order date	Order dispatched	Order delivered	Invoice paid	Manager	Department	Total Sale Amount
2	1	Rory Clayton	Laptop	Paris	Elizabeth Jackson	C200	5000	IN-123-C-001	1	3/1/2021	3/2/2021	3/5/2021	Yes	Jaye Marquez	Customer Service	5000
3	2	Bradley Hester	Desktop	Paris	Victoria James	C201	5000	IN-123-C-002	1	3/2/2021	3/3/2021	3/6/2021	Yes	Alfie Burrows	Customer Service	5000
4	3	Alice Baker	Mobile phone	Paris	Jane Harris	C202	2000	IN-123-C-003	1	3/3/2021	3/4/2021	3/7/2021	Yes	Shea Walton	Customer Service	2000
5	4	Zachery Carlson	Tablet	Paris	Phil Rampling	C203	3000	IN-123-C-004	2	3/4/2021	3/5/2021	3/8/2021	Yes	Ziggy Simons	Customer Service	6000
6	5	Isai Harrison	Laptop	Paris	Simon Campbell	C204	5000	IN-123-C-005	1	3/5/2021	3/6/2021	3/9/2021	Yes	Jose Woolley	Customer Service	5000
7	6	Mira Neal	Desktop	Paris	Lucas Hughes	C205	5000	IN-123-C-006	1	3/6/2021	3/7/2021	3/10/2021	Yes	Cali Thomas	Customer Service	5000
8	7	Trinity Green	Mobile phone	Paris	Bella Duncan	C206	2000	IN-123-C-007	1	3/7/2021	3/8/2021	3/11/2021	Yes	Rudi Glenn	Customer Service	2000
9	8	Samara Cox	Tablet	Paris	Joshua Sanderson	C207	3000	IN-123-C-008	1	3/8/2021	3/9/2021	3/12/2021	Yes	Aston Callaghan	Customer Service	3000
10	9	Maryjane Villa	Laptop	Paris	Leighton Morin	C208	5000	IN-123-C-009	1	3/9/2021	3/10/2021	3/13/2021	Yes	Loki Bain	Customer Service	5000
11	10	Ayden Kaiser	Desktop	Berlin	Holly Decker	C209	5000	IN-123-C-010	1	3/10/2021	3/11/2021	3/14/2021	Yes	Kobi Wade	Customer Service	5000
12	11	Hayden Shaffer	Mobile phone	Berlin	Dean Crane	C210	2000	IN-123-C-011	3	3/11/2021	3/12/2021	3/15/2021	Yes	Star Rutledge	Customer Service	6000
13	12	Dominic Raymond	Tablet	Vienna	Safiya Elliott	C211	3000	IN-123-C-012	1	3/12/2021	3/13/2021	3/16/2021	Yes	Baran Orozco	Customer Service	3000
14	13	Urijah Jenkins	Laptop	Vienna	Macsen Mcmahon	C212	5000	IN-123-C-013	1	3/13/2021	3/14/2021	3/17/2021	No	Bo Mueller	Customer Service	5000
15	14	Tia Love	Laptop	Vienna	Emmanuella Small	C213	5000	IN-123-C-014	1	3/14/2021	3/15/2021	3/18/2021	No	Nadia Buckley	Customer Service	5000
16	15	Cloe English	Laptop	Berlin	Zi Coulson	C214	5000	IN-123-C-015	1	3/15/2021	3/16/2021	3/19/2021	No	Divine Dejesus	Customer Service	5000
17	16	Zackery Marshall	Laptop	Berlin	Iyla Richardson	C215	5000	IN-123-C-016	2	3/16/2021	3/17/2021	3/20/2021	No	Shelly Downs	Sales	10000
18	17	Luna Reed	Laptop	Berlin	Huseyin Bourne	C216	5000	IN-123-C-017	1	3/17/2021	3/18/2021	3/21/2021	No	Siobhan Bernal	Sales	5000
19	18	Melanie Benson	Laptop	Berlin	Burhan Howarth	C217	5000	IN-123-C-018	1	3/18/2021	3/19/2021	3/22/2021	No	Jan Bob	Sales	5000
20	19	Lucian Hayes	Laptop	Berlin	Kaidan Felix	C218	5000	IN-123-C-019	1	3/19/2021	3/20/2021	3/23/2021	No	Lucca Markham	Sales	5000
21	20	Haven Norris	Laptop	Berlin	Micah Rowley	C219	5000	IN-123-C-020	1	3/20/2021	3/21/2021	3/24/2021	No	Coby Franklin	Sales	5000
22	21	Xavier Wade	Laptop	Berlin	Mikolaj Marks	C220	5000	IN-123-C-021	8	3/21/2021	3/22/2021	3/25/2021	No	Belle Hanna	Sales	40000
23	22	Tomas Huber	Laptop	Rome	Zaynab Gray	C221	5000	IN-123-C-022	1	3/22/2021	3/23/2021	3/26/2021	No	Wade Park	Sales	5000
24	23	Aryana Randolph	Tablet	Rome	Nelson Mooney	C222	3000	IN-123-C-023	1	3/23/2021	3/24/2021	3/27/2021	No	Ada Roach	Sales	3000
25	24	Jakobe Oneal	Tablet	Berlin	Kamal Albert	C223	3000	IN-123-C-024	1	3/24/2021	3/25/2021	3/28/2021	No	Skye Parkes	Sales	3000
26	25	Adelyn Stephens	Tablet	Berlin	Camilla Gilmore	C224	3000	IN-123-C-025	1	3/25/2021	3/26/2021	3/29/2021	No	Jorge Shepherd	Sales	3000
27	26	Dale Ortiz	Tablet	Berlin	Meadow Mclean	C225	3000	IN-123-C-026	1	3/26/2021	3/27/2021	3/30/2021	No	Nikki Garrison	Sales	3000
28	27	Conrad Daniel	Tablet	Berlin	Esme-Rose Currie	C226	3000	IN-123-C-027	1	3/27/2021	3/28/2021	3/31/2021	No	Ira Mills	Sales	3000
29	28	Raina Novak	Tablet	Berlin	Darren Preston	C227	3000	IN-123-C-028	1	3/28/2021	3/29/2021	4/1/2021	No	Yassin Mccartney	Sales	3000
30	29	Kadence Barber	Tablet	Berlin	Lukas French	C228	3000	IN-123-C-029	1	3/29/2021	3/30/2021	4/2/2021	No	Bobbie Long	Sales	3000
31	30	Salvador Cook	Tablet	Berlin	Kyran Rhodes	C229	3000	IN-123-C-030	7	3/30/2021	3/31/2021	4/3/2021	No	Quinn Gallegos	Sales	21000
32	31	Jeremiah Bennett	Tablet	Bratislava	Erik Weaver	C230	3000	IN-123-C-031	1	3/31/2021	4/1/2021	4/4/2021	No	Shayne Lowery	Sales	3000
33	32	Jake Sosa	Tablet	Bratislava	Alex Rodriguez	C231	3000	IN-123-C-032	1	4/1/2021	4/2/2021	4/5/2021	No	Charli Bishop	Sales	3000
34	33	Josephine Mcknight	Mobile phone	Bratislava	Nafeesa Conner	C232	2000	IN-123-C-033	1	4/2/2021	4/3/2021	4/6/2021	No	Nevaeh Coles	Sales	2000
35	34	Billy Greer	Mobile phone	Bratislava	Eliot Langley	C233	2000	IN-123-C-034	1	4/3/2021	4/4/2021	4/7/2021	No	Roma Evans	Sales	2000
36	35	Anabel Joyce	Mobile phone	Bratislava	Muna Cannon	C234	2000	IN-123-C-035	1	4/4/2021	4/5/2021	4/8/2021	Yes	Arya Adam	Sales	2000
37	36	Aniya Lowery	Mobile phone	Bratislava	Abid Walton	C235	2000	IN-123-C-036	1	4/5/2021	4/6/2021	4/9/2021	Yes	Kira Estes	Sales	2000
38	37	Maddison Bonilla	Mobile phone	Bratislava	Kodi Rawlings	C236	2000	IN-123-C-037	1	4/6/2021	4/7/2021	4/10/2021	Yes	Devon Mathews	Sales	2000
39	38	Ethen Frey	Mobile phone	Bratislava	Allison Snyder	C237	2000	IN-123-C-038	4	4/7/2021	4/8/2021	4/11/2021	Yes	Macauley Yates	Sales	8000
40	39	Rocco Hogan	Mobile phone	Bratislava	Fraser Armstrong	C238	2000	IN-123-C-039	1	4/8/2021	4/9/2021	4/12/2021	Yes	Fallon Noel	Sales	2000
41	40	Luna Evans	Mobile phone	Bratislava	Leyton Tyson	C239	2000	IN-123-C-040	1	4/9/2021	4/10/2021	4/13/2021	Yes	Ali Tyson	Sales	2000
42	41	Sofia Cruz	Mobile phone	Bratislava	Prisha Kirby	C240	2000	IN-123-C-041	1	4/10/2021	4/11/2021	4/14/2021	Yes	Karis Munoz	Sales	2000
43	42	Kaitlin Knox	Mobile phone	Vienna	Alena Davey	C241	2000	IN-123-C-042	1	4/11/2021	4/12/2021	4/15/2021	Yes	Kristi Peters	Sales	2000
44	43	Gemma Mcpherson	Mobile phone	Vienna	Lexi-Mai Christian	C242	2000	IN-123-C-043	1	4/12/2021	4/13/2021	4/16/2021	Yes	Dylan Parks	Sales	2000
45	44	Giancarlo Fleming	Mobile phone	Vienna	Hebe Thorne	C243	2000	IN-123-C-044	1	4/13/2021	4/14/2021	4/17/2021	Yes	Robbie Hodge	Sales	2000
46	45	Carlo Graves	Mobile phone	Bratislava	Kaira Gardiner	C244	2000	IN-123-C-045	1	4/14/2021	4/15/2021	4/18/2021	Yes	Corrie Matthews	Sales	2000
47	46	Vance Webb	Mobile phone	Barcelona	Avneet Harrington	C245	2000	IN-123-C-046	1	4/15/2021	4/16/2021	4/19/2021	Yes	Simone Hills	Sales	2000
48	47	Kamari Friedman	Mobile phone	Barcelona	Ruby-Mae Turner	C246	2000	IN-123-C-047	5	4/16/2021	4/17/2021	4/20/2021	Yes	Dane Goldsmith	Sales	10000
49	48	Carmen Schwartz	Mobile phone	Barcelona	Thiago Pate	C247	2000	IN-123-C-048	1	4/17/2021	4/18/2021	4/21/2021	Yes	Ezra Hawes	Sales	2000
50	49	Cristina Haney	Mobile phone	Barcelona	Christine Garcia	C248	2000	IN-123-C-049	1	4/18/2021	4/19/2021	4/22/2021	Yes	Kacy Richmond	Sales	2000
51	50	Payton Hurst	Mobile phone	Barcelona	Albie Villegas	C249	2000	IN-123-C-050	1	4/19/2021	4/20/2021	4/23/2021	Yes	Jett Coleman	Sales	2000
Data

Sales data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Sales Agent ID	Sales Agent Name	Product	Location	Customer	Customer ID	Price	Order number	Quantity	Order date	Order dispatched	Order delivered	Invoice paid	Manager	Department	Total Sale Amount
2	10	Ayden Kaiser	Desktop	Berlin	Holly Decker	C209	5000	IN-123-C-010	1	10-Mar-21	11-Mar-21	14-Mar-21	Yes	Kobi Wade	Customer Service	5000
3	11	Hayden Shaffer	Mobile phone	Berlin	Dean Crane	C210	2000	IN-123-C-011	3	11-Mar-21	12-Mar-21	15-Mar-21	Yes	Star Rutledge	Customer Service	6000
4	15	Cloe English	Laptop	Berlin	Zi Coulson	C214	5000	IN-123-C-015	1	15-Mar-21	16-Mar-21	19-Mar-21	No	Divine Dejesus	Customer Service	5000
5	16	Zackery Marshall	Laptop	Berlin	Iyla Richardson	C215	5000	IN-123-C-016	2	16-Mar-21	17-Mar-21	20-Mar-21	No	Shelly Downs	Sales	10000
6	17	Luna Reed	Laptop	Berlin	Huseyin Bourne	C216	5000	IN-123-C-017	1	17-Mar-21	18-Mar-21	21-Mar-21	No	Siobhan Bernal	Sales	5000
7	18	Melanie Benson	Laptop	Berlin	Burhan Howarth	C217	5000	IN-123-C-018	1	18-Mar-21	19-Mar-21	22-Mar-21	No	Jan Bob	Sales	5000
8	19	Lucian Hayes	Laptop	Berlin	Kaidan Felix	C218	5000	IN-123-C-019	1	19-Mar-21	20-Mar-21	23-Mar-21	No	Lucca Markham	Sales	5000
9	20	Haven Norris	Laptop	Berlin	Micah Rowley	C219	5000	IN-123-C-020	1	20-Mar-21	21-Mar-21	24-Mar-21	No	Coby Franklin	Sales	5000
10	21	Xavier Wade	Laptop	Berlin	Mikolaj Marks	C220	5000	IN-123-C-021	8	21-Mar-21	22-Mar-21	25-Mar-21	No	Belle Hanna	Sales	40000
11	30	Salvador Cook	Tablet	Berlin	Kyran Rhodes	C229	3000	IN-123-C-030	7	30-Mar-21	31-Mar-21	3-Apr-21	No	Quinn Gallegos	Sales	21000
12	38	Ethen Frey	Mobile phone	Bratislava	Allison Snyder	C237	2000	IN-123-C-038	4	7-Apr-21	8-Apr-21	11-Apr-21	Yes	Macauley Yates	Sales	8000
13	47	Kamari Friedman	Mobile phone	Barcelona	Ruby-Mae Turner	C246	2000	IN-123-C-047	5	16-Apr-21	17-Apr-21	20-Apr-21	Yes	Dane Goldsmith	Sales	10000
Filter

Cell Formulas
Range	Formula
A2:P13	A2	=FILTER(Data!A:P,(ISNUMBER(SEARCH("b",Data!D:D))*(Data!P:P>=5000)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Data!_FilterDatabase	=Data!$A$1:$P$51	A2

Sales data.xlsx
	A	B
1	Location	Country
2	Paris	France
3	Berlin	Germany
4	Budapest	Hungary
5	Tczew	Poland
6	Oslo	Norway
7	Rome	Italy
8	Bratislava	Slovakia
9	Vienna	Austria
10	Barcelona	Spain
11	Lisbon	Portugal
City_Country

 

[Fluff]

Thanks for that.
The only way I can think of at the moment is something like

=LET(f,FILTER(Data!A:P,(ISNUMBER(SEARCH("b",Data!D:D))*(Data!P:P>=5000))),x,XLOOKUP(INDEX(f,,4),City_Country!A2:A100,City_Country!B2:B100,"",0),CHOOSE(SEQUENCE(,16),f,f,f,x,INDEX(f,,5),INDEX(f,,6)))

you will need to add the rest of the index function to the end in the same manner as I've shown

 

[Fluff]

Ignore my previous post, try this instead.

=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),IF(s=4,x,INDEX(f,SEQUENCE(ROWS(f)),s)))

 

[UserI]

Ignore my previous post, try this instead.

=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),IF(s=4,x,INDEX(f,SEQUENCE(ROWS(f)),s)))

Thank you so much! It works perfectly!