filter multiple columns

[ExcelNewbie2020]

i have this source data. each event that has entries in 3 columns (country/time/date).

idk if this is possible.. i need to filter those 3 columns based on events..like if i choose a country in cell B25, it will automatically populate information related to that country..please see expected result.. thank you

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	source data
3	event	country	time(min)	date	country	time(min)	date	country	time(min)	date	country	time(min)	date	country	time(min)	date
4	event 1	Sierra Leone	20	2-Apr-2024	Romania	30	19-May-2024	Sierra Leone	60	17-Apr-2024				Sierra Leone	15	21-May-2024
5	event 2	Romania	30	8-May-2024
6	event 3				North Macedonia	30	3-Apr-2024							Mexico	20	6-May-2024
7	event 4	Ecuador	30	11-May-2024
8	event 5
9	event 6							North Macedonia	10	2-May-2024
10	event 7										Guatemala	30	9-Apr-2024
11	event 8	Romania	30	22-Apr-2024	Sierra Leone	60	30-Apr-2024							Marshall Islands	30	7-Apr-2024
12	event 9	Nicaragua	30	23-Apr-2024
13	event 10
14	event 11
15	event 12	Costa Rica	20	10-Apr-2024
16	event 13	Equatorial Guinea	60	1-Apr-2024	Cameroon	30	10-May-2024				Sierra Leone	25	11-May-2024
17	event 14	Romania	10	11-May-2024
18	event 15
19	event 16	Peru	30	1-Apr-2024	North Macedonia	60	15-May-2024
20	event 17													Sierra Leone	60	23-May-2024
21	event 18	Sierra Leone	30	8-Apr-2024				Egypt	15	10-Apr-2024
22
23
24	expected result
25	country selection	Sierra Leone
26
27	event	country	time(min)	date	country	time(min)	date	country	time(min)	date
28	event 1	Sierra Leone	20	2-Apr-2024	Sierra Leone	60	17-Apr-2024	Sierra Leone	15	21-May-2024
29	event 2
30	event 3
31	event 4
32	event 5
33	event 6
34	event 7
35	event 8	Sierra Leone	60	30-Apr-2024
36	event 9
37	event 10
38	event 11
39	event 12
40	event 13	Sierra Leone	25	11-May-2024
41	event 14
42	event 15
43	event 16
44	event 17	Sierra Leone	60	23-May-2024
45	event 18	Sierra Leone	30	8-Apr-2024
Sheet1

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	source data
2	event	country	time(min)	date	country	time(min)	date	country	time(min)	date	country	time(min)	date	country	time(min)	date
3	event 1	Sierra Leone	20	4/2/24	Romania	30	5/19/24	Sierra Leone	60	4/17/24				Sierra Leone	15	5/21/24
4	event 2	Romania	30	5/8/24
5	event 3				North Macedonia	30	4/3/24							Mexico	20	5/6/24
6	event 4	Ecuador	30	5/11/24
7	event 5
8	event 6							North Macedonia	10	5/2/24
9	event 7										Guatemala	30	4/9/24
10	event 8	Romania	30	4/22/24	Sierra Leone	60	4/30/24							Marshall Islands	30	4/7/24
11	event 9	Nicaragua	30	4/23/24
12	event 10
13	event 11
14	event 12	Costa Rica	20	4/10/24
15	event 13	Equatorial Guinea	60	4/1/24	Cameroon	30	5/10/24				Sierra Leone	25	5/11/24
16	event 14	Romania	10	5/11/24
17	event 15
18	event 16	Peru	30	4/1/24	North Macedonia	60	5/15/24
19	event 17													Sierra Leone	60	5/23/24
20	event 18	Sierra Leone	30	4/8/24				Egypt	15	4/10/24
21
22
23	expected result
24	country selection	Sierra Leone
25
26	event
27	event 1	Sierra Leone	20	4/2/24				Sierra Leone	60	4/17/24				Sierra Leone	15	5/21/24
28	event 2
29	event 3
30	event 4
31	event 5
32	event 6
33	event 7
34	event 8				Sierra Leone	60	4/30/24
35	event 9
36	event 10
37	event 11
38	event 12
39	event 13										Sierra Leone	25	5/11/24
40	event 14
41	event 15
42	event 16
43	event 17													Sierra Leone	60	5/23/24
44	event 18	Sierra Leone	30	4/8/24
Sheet1

Cell Formulas
Range	Formula
B27:P44	B27	=LET(w,WRAPROWS(TOCOL(B3:P20),3),f,IF(CHOOSECOLS(w,1)=B24,w,""),WRAPROWS(TOCOL(f),COLUMNS(B2:P2),""))
Dynamic array formulas.

 

[ExcelNewbie2020]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	source data
2	event	country	time(min)	date	country	time(min)	date	country	time(min)	date	country	time(min)	date	country	time(min)	date
3	event 1	Sierra Leone	20	4/2/24	Romania	30	5/19/24	Sierra Leone	60	4/17/24				Sierra Leone	15	5/21/24
4	event 2	Romania	30	5/8/24
5	event 3				North Macedonia	30	4/3/24							Mexico	20	5/6/24
6	event 4	Ecuador	30	5/11/24
7	event 5
8	event 6							North Macedonia	10	5/2/24
9	event 7										Guatemala	30	4/9/24
10	event 8	Romania	30	4/22/24	Sierra Leone	60	4/30/24							Marshall Islands	30	4/7/24
11	event 9	Nicaragua	30	4/23/24
12	event 10
13	event 11
14	event 12	Costa Rica	20	4/10/24
15	event 13	Equatorial Guinea	60	4/1/24	Cameroon	30	5/10/24				Sierra Leone	25	5/11/24
16	event 14	Romania	10	5/11/24
17	event 15
18	event 16	Peru	30	4/1/24	North Macedonia	60	5/15/24
19	event 17													Sierra Leone	60	5/23/24
20	event 18	Sierra Leone	30	4/8/24				Egypt	15	4/10/24
21
22
23	expected result
24	country selection	Sierra Leone
25
26	event
27	event 1	Sierra Leone	20	4/2/24				Sierra Leone	60	4/17/24				Sierra Leone	15	5/21/24
28	event 2
29	event 3
30	event 4
31	event 5
32	event 6
33	event 7
34	event 8				Sierra Leone	60	4/30/24
35	event 9
36	event 10
37	event 11
38	event 12
39	event 13										Sierra Leone	25	5/11/24
40	event 14
41	event 15
42	event 16
43	event 17													Sierra Leone	60	5/23/24
44	event 18	Sierra Leone	30	4/8/24
Sheet1

Cell Formulas
Range	Formula
B27:P44	B27	=LET(w,WRAPROWS(TOCOL(B3:P20),3),f,IF(CHOOSECOLS(w,1)=B24,w,""),WRAPROWS(TOCOL(f),COLUMNS(B2:P2),""))
Dynamic array formulas.

thanks man..really appreciate it.. maybe its possible to move the values to the left?

 

[Cubist]

Yes, but it has a character limit of ~32,000 characters. Try:

=LET(
w,WRAPROWS(TOCOL(B3:P20),3),
f,IF(CHOOSECOLS(w,1)=B24,w,""),
k,WRAPROWS(TOCOL(f),COLUMNS(B2:P2),""),
TEXTSPLIT(TEXTJOIN(",",FALSE,BYROW(k,LAMBDA(br,TEXTJOIN("|",TRUE,br)))),"|",",",FALSE,,""))

 

[ExcelNewbie2020]

Yes, but it has a character limit of ~32,000 characters. Try:

=LET(
w,WRAPROWS(TOCOL(B3:P20),3),
f,IF(CHOOSECOLS(w,1)=B24,w,""),
k,WRAPROWS(TOCOL(f),COLUMNS(B2:P2),""),
TEXTSPLIT(TEXTJOIN(",",FALSE,BYROW(k,LAMBDA(br,TEXTJOIN("|",TRUE,br)))),"|",",",FALSE,,""))

thanks again but it convert the date and the time into text. tried format cells but it did nothing..

 

[Cubist]

How about this?

=LET(
w,WRAPROWS(TOCOL(B3:P20),3),
f,IF(CHOOSECOLS(w,1)=B24,w,""),
k,WRAPROWS(TOCOL(f),COLUMNS(B2:P2),""),
t,TEXTSPLIT(TEXTJOIN(",",FALSE,BYROW(k,LAMBDA(br,TEXTJOIN("|",TRUE,br)))),"|",",",FALSE,,""),IFERROR(--t,t))

 

[ExcelNewbie2020]

How about this?

=LET(
w,WRAPROWS(TOCOL(B3:P20),3),
f,IF(CHOOSECOLS(w,1)=B24,w,""),
k,WRAPROWS(TOCOL(f),COLUMNS(B2:P2),""),
t,TEXTSPLIT(TEXTJOIN(",",FALSE,BYROW(k,LAMBDA(br,TEXTJOIN("|",TRUE,br)))),"|",",",FALSE,,""),IFERROR(--t,t))

it works..thanks man..

 

[Cubist]

You're welcome. Cheers.