How do I create a table that then considers whether or not clients appear in other tables?

[HelpPlease123]

Hi Guys,

Pretty new to excel so hoping there's a simple answer to this that I'm not aware of.

I work in marketing insight and part of my role is selecting the best customers to send each of our direct mail campaigns to, and I want to create a workbook in where I can walk through our monthly 'Direct Mail Plan' with the relevant people using a 'selection dashboard' where I can select the clients we want using report filters/slicers, and then when I move onto the next Direct Mail selection I want to have a flag next to the clients that appeared in the previous selection.

I've used a COUNTIF function in the past to count the customer ID (1) if they appear in the table above, but this feels a little too manual/open to error when adjustments are made to the selections tables, so I'm wondering if there's a more efficient way of doing this so that it's a nice user friendly workbook? I'm not familiar with powerpivot/power query but I can learn if your suggested method requires it.

Below is a rough example of the dataset and underneath is an idea of what I'd like the finished product to look like...

Example of Dataset (the real thing will be around 500k records):

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
3	ClientID	EUROPEAN_RANK	LONGHAUL_RANK	OCEAN_RANK	RIVER_RANK	SOLO_RANK	CITY_RANK	OTHER_RANK	Segmentation_RANK	EUROPEAN	LONGHAUL	OCEAN	RIVER	SOLO	CITY	OTHER	Segmentation	Age	AverageSpend_per_pax	LastBookingSpend_per_pax	LastActivityDate	LastEnquiryDate	LastEnquiryType	LastBookingDate	LastBookingType
4	1	1	1	NULL	NULL	NULL	NULL	NULL	1	1	1	0	0	0	0	0	BE2 Dormant Medium - European	80-85	1069	£1000-£1500	6-12 months	2022	OTHER	2017	European
5	2	2	NULL	NULL	1	NULL	NULL	NULL	2	1	0	0	1	0	0	1	BE2 Dormant Medium - European	80-85	1127	1000	12-18 monthd	2022	EUROPEAN	2017	European
6	3	3	NULL	NULL	2	NULL	NULL	NULL	1	1	0	0	1	0	0	0	AB1 Active Best - River	80-85	1488	1400	6-12 months	2023	RIVER	2022	River
7	4	NULL	2	NULL	3	NULL	NULL	NULL	2	1	1	0	1	0	0	0	AB1 Active Best - River	70-75	2101	2000	6-12 months	2023	RIVER	2023	River
Sheet1

Idea of Final Product:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
9
10		THIS WOULD APPEAR IN SHEET 2				NOT FILTERED TO VISUALISE DATA, BUT HERE I WOULD FILTER TO ONLY SELECT CLIENTS: 3&4
11		Direct Mail: 001 (RIVER)									CATEGORY	CUSTOMERS
12	CustomerID	EUROPEAN	LONGHAUL	OCEAN	RIVER	OTHER	Age	AverageSpend_per_pax	LastActivityDate		EUROPEAN	4
13	1	1	1	0	0	0	80-85	£1,069.00	6-12 months		LONGHAUL	2
14	2	1	0	0	1	1	80-85	£1,127.00	12-18 monthd		OCEAN	0
15	3	1	0	0	1	0	80-85	£1,488.00	6-12 months		RIVER	3
16	4	1	1	0	1	0	70-75	£2,101.00	6-12 months		OTHER	1
17
18
19		THIS WOULD APPEAR IN SHEET 3				NOT FILTERED TO VISUALISE DATA, BUT HERE I WOULD FILTER TO ONLY SELECT CLIENTS: 2, 3 AND 4
20		Direct Mail: 002 (EUROPEAN)										CATEGORY	CUSTOMERS
21	CustomerID	EUROPEAN	LONGHAUL	OCEAN	RIVER	OTHER	Age	AverageSpend_per_pax	LastActivityDate	DM 001		EUROPEAN	4
22	1	1	1	0	0	0	80-85	£1,069.00	6-12 months	0		LONGHAUL	2
23	2	1	0	0	1	1	80-85	£1,127.00	12-18 monthd	1		OCEAN	0
24	3	1	0	0	1	0	80-85	£1,488.00	6-12 months	1		RIVER	3
25	4	1	1	0	1	0	70-75	£2,101.00	6-12 months	1		OTHER	1
26
27
28		THIS WOULD APPEAR IN SHEET 4				NOT FILTERED TO VISUALISE DATA, BUT HERE I WOULD FILTER TO ONLY SELECT CLIENTS: 3 AND 4
29		Direct Mail: 003 (OCEAN)											CATEGORY	CUSTOMERS
30	CustomerID	EUROPEAN	LONGHAUL	OCEAN	RIVER	OTHER	Age	AverageSpend_per_pax	LastActivityDate	DM 001	DM 002		EUROPEAN	4
31	1	1	1	0	0	0	80-85	£1,069.00	6-12 months	0	1		LONGHAUL	2
32	2	1	0	0	1	1	80-85	£1,127.00	12-18 monthd	1	1		OCEAN	0
33	3	1	0	0	1	0	80-85	£1,488.00	6-12 months	1	0		RIVER	3
34	4	1	1	0	1	0	70-75	£2,101.00	6-12 months	1	0		OTHER	1
35
36
37		THIS WOULD APPEAR IN SHEET 5				NOT FILTERED TO VISUALISE DATA, BUT HERE I WOULD FILTER TO ONLY SELECT CLIENTS: 3 AND 4
38		Direct Mail: 004 (ALL)												CATEGORY	CUSTOMERS
39	CustomerID	EUROPEAN	LONGHAUL	OCEAN	RIVER	OTHER	Age	AverageSpend_per_pax	LastActivityDate	DM 001	DM 002	DM 003		EUROPEAN	4
40	1	1	1	0	0	0	80-85	£1,069.00	6-12 months	0	1	0		LONGHAUL	2
41	2	1	0	0	1	1	80-85	£1,127.00	12-18 monthd	1	1	0		OCEAN	0
42	3	1	0	0	1	0	80-85	£1,488.00	6-12 months	1	0	1		RIVER	3
43	4	1	1	0	1	0	70-75	£2,101.00	6-12 months	1	0	1		OTHER	1
44
45
46	EXAMPLE OF FILTER USE: IF CUSTOMERS RECEIVED 'DM003' THEN THEY CANNOT BE SELECTED. APPLY DM003 FLAG = 0 AND WE CAN THEN SELECT FROM THE REMAINING CUSTOMERS. COUNT TABLE AT THE SIDE UPDATES TO SHOW HOW MANY CUSTOMERS THERE ARE LEFT FOR US TO SELECT FROM.													CATEGORY	CUSTOMERS
47														EUROPEAN	2
48	CustomerID	EUROPEAN	LONGHAUL	OCEAN	RIVER	OTHER	Age	AverageSpend_per_pax	LastActivityDate	DM 001	DM 002	DM 003		LONGHAUL	1
49	1	1	1	0	0	0	80-85	£1,069.00	6-12 months	0	1	0		OCEAN	0
50	2	1	0	0	1	1	80-85	£1,127.00	12-18 monthd	1	1	0		RIVER	1
53														OTHER	1
54
Sheet1

Cell Formulas
Range	Formula
L12	L12	=SUM(B13:B16)
L13	L13	=SUM(C13:C16)
L14	L14	=SUM(D13:D16)
L15	L15	=SUM(E13:E16)
L16	L16	=SUM(F13:F16)
O47	O47	=SUM(B49:B50)
O48	O48	=SUM(C49:C50)
O49	O49	=SUM(D49:D50)
O50	O50	=SUM(E49:E50)
O53	O53	=SUM(F49:F50)