Checking Large Data table for text matched in a row to be selected by an id

[rachenk]

Hi All,

I have a workbook containing a large amount of data that I need to set up to easily analyse if something is not as predicted. Here are some of the sheets I have in the workbook;

Services - a large list of services (more than 250,000 rows) provided (or scheduled) to our clients; each one has the Client's ID number, Date of Service, Service Type, Service Status Column, etc.
Client Contracts - On another sheet, I have a list of all of the Clients and their contract information across the row. The Client ID number is in column GJ, and the types of Services (in text format for the name) we have in their contract/budget to deliver are in the row for the client in Columns CL to GH

I need to work out, for each client if any of the service types listed for them on the Services sheet are not in the Service type list for the client in their row on the Client Contracts Sheet. If there is a Service that was not included in the contract, I need to highlight it or make it in some way for ease of reviewing. This also needs to work in a way that when the data changes on either the services or Client Contracts sheets, it uses the updated data

For example, I need to check if my client Johnny Smith (with ID 1509) has received (or is scheduled to receive) any services that were not negotiated in his contract of service so we can review and contact Johnny to discuss.

 

[RobP]

Hi, not so easy with the amount of data you have, and limited data I can see on the ClientContracts sheet. But this my take on a suggestion for you - and it has to be formula based as you want it to auto update with new data. The down side is you have a formula to copy down 250k rows .. but thats a few seconds work once you've set the parameters in the formula I've created.

The absolute data in my sheets (as a copy of your image) might not be perfect, Services sheet is ok, ClientContracts sheet is not clean at all - a result of using .png images to show your problem.

Essentially, I added a Column on the "Services" sheet in "W", which contains the formula.

RESULTS:

client id is not found on the ClientContracts sheet, it inserts "NCC", meaning "No Customer Contract" is setup at all for any service.
client id is matched, but ServiceType is not found on ClientContracts, then it inserts "Not on Contract" (= a review is needed)
client id is matched, and it finds the service on contract sheet, it will leave the cell blank ("") - as no review needed.

Book1
	A		C	D		F	G	H	I	J		L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Service		Client ID	Date		ServiceType	Duration	Units	CANCEL	Status		Held Value								Claimed	Cost/Pa		Review ?
2	168977		1149	24/06/2022		.Support Worker (Daily Activlty)	7.5	Hrs	A Worker CE	CA		$o.oo	0	0	0	$o.oo	$o.oo	$o.oo	$o.oo $o.oo		0%
3	178666		1509	24/06/2022		.Sleepover Penalty (Claimed)	4	Hrs		co		$o.oo	$239.24	$239.24	$ooo	$163.08	$o.oo	$o.oo	$163.08	$76.16	68%		NCC
4	179161		1509	24/06/2022		.Support Worker (DailyActivity)	4	Hrs		co		$o.oo	$239.24	$239.24	$o.oo	$145.60	$o.oo	$o.oo	$145.60	$9.364	61%		NCC
5	179209		1509	24/06/2022		.Sleepover Penalty (Claimed)	1	Hrs		co		$o.oo	$65.82	$65.82	$o.oo	$40.77	$o.oo	$o.oo	$40.77	$25.05	62%		NCC
6	179257		1509	24/06/2022		.Sleepover Allowance 01_010_0107_1_1	8	Hrs		co		$o.oo	$242.95	$242.95	$o.oo	$52.86	$o.oo	$o.oo	$52.86	$190.09	22%		NCC
7	208909		1530	24/06/2022		.Support Worker (DailyActivity) 1:2	8	Hrs		co		$o.oo	$239.20	$o.oo	-$239.20	$152.32	$o.oo	$o.oo	$152.32	$86.88	0%		NCC
8	189254		1578	24/06/2022		ILO Days 01_046_0115_1_1 Complex	1	Days		co		$o.oo	$990.28	$990.28	$o.oo	$451.33	$o.oo	$o.oo	$451.33	$5.3.995	46%		NCC
9	189506		1433	24/06/2022		.Support Worker ( DailyActivity)	6	Hrs		co		$o.oo	$358.86	$358.86	$o.oo	$190.62	$o.oo	$o.oo	$190.62	$168.24	53%		NCC
10	190542		1439	24/06/2022		.Support Worker (020) .24hr Shift 24.00 Hß	24	Hrs		co		$o.oo	$o.oo	$o.oo	$o.oo	$371.78	$o.oo	$o.oo	$371.78	-$371.78	0%		NCC
11	192829		1512	24/06/2022		.Support Worker (Social Community Participat	5	Hrs		co		$o.oo	$299.05	$299.05	$ooo	$182.oo	$o.oo	$o.oo	$182.oo	$117.05	61%		NCC
12	193939		1527	24/06/2022		.Support Worker (DailyActivity)	4	Hrs		co		$o.oo	$239.24	$239.24	$o.oo	$145.60	$o.oo	$o.oo	$145.60	$93.64	61%		NCC
13	194205		273	24/06/2022		CB (Improved Life Choice) PM MthlyFee 14_034	2	Hrs		co		$o.oo	$208.90	$208.90	$o.oo	$o.oo	$o.oo	$o.oo	$o.oo	$208.90	0%		Not on Contract
14	197318		273	24/06/2022		.SupportWorker (020)	4	Hrs		co		$o.oo	$o.oo	$o.oo	$o.oo	$127.44	$o.oo	$o.oo	$127.44	-$127.44	0%		Not on Contract
15	198518		1458	24/06/2022		.Sleepover Penalty (Clamed)	4	Hrs		co		$o.oo	$263.28	$263.28	$ooo	$163.08	$o.oo	$o.oo	$163.08	$100.20	62%		NCC
16	198893		1449	24/06/2022		.Support Worker (DailyActivity)	4	Hrs		co		$o.oo	$239.24	$239.24	$o.oo	$o.oo	$o.oo	$o.oo	$o.oo	$2.3.224	0%		NCC
17	199176		1449	24/06/2022		.Support Worker (DailyActivity)	3	Hrs		co		$o.oo	$179.43	$179.43	$o.oo	$109.20	$o.oo	$o.oo	$109.20	$70.23	61%		NCC
18	201384		1564	24/06/2022		SIL Standard Supports	5.5	Hrs	No Worker A	CA		$o.oo	$o.oo	$o.oo	$o.oo	$o.oo	$o.oo	$o.oo	$o.oo	$o.oo	0%		NCC
19	201557		1564	24/06/2022		SIL Sleepover 01_832_0115_1_1	8	Hrs		co		$o.oo	$243.22	$243.22	$ooo	$52.86	$o.oo	$o.oo	$52.86	$190.36	22%		NCC
20	201603		1564	24/06/2022		SIL Sleepover Penalty(C1aimed)	4	Hrs		co		$o.oo	$252.28	$252.28	$o.oo	$163.08	$o.oo	$o.oo	$163.08	$89.20	65%		NCC
21	202872		1441	24/06/2022		.Support Worker (DailyActivity)	3	Hrs		co		$o.oo	$179.43	$179.43	$o.oo	$109.20	$o.oo	$o.oo	$109.20	$70.23	61%		NCC
22	202977		1473	24/06/2022		.Support Worker (Daily Activity)	5	Hrs		co		$o.oo	$299.05	$299.05	$o.oo	$182.oo	$o.oo	$o.oo	$182.oo	$117.05	61%		NCC
23	205379		1285	24/06/2022		.Support Worker (DailyActivity)	5	Hrs		co		$o.oo	$285.50	$285.50	$ooo	$182.oo	$o.oo	$o.oo	$182.oo	$103.50	64%		NCC
24	205631		1285	24/06/2022		.Support Worker (Social Community Participat	2	Hrs		co		$o.oo	$126.68	$126.68	$o.oo	$72.80	$o.oo	$o.oo	$72.80	$5.388	57%		NCC
25	206055		1524	24/06/2022		.Support Coresident (DailyActlvity)	7	Hrs		co		$o.oo	$277.76	$277.76	$o.oo	$172.20	$o.oo	$o.oo	$172.20	$105.56	62%		Not on Contract
26	206363		1541	24/06/2022		SIC Standard Supports	8	Hrs		co		$o.oo	$458.40	$458.40	$o.oo	$291.20	$o.oo	$o.oo	$291.20	$167.20	64%		NCC
27	207023		1268	24/06/2022		.Sleepover Penalty (Clamed)	4.5	Hrs		SU		$o.oo	$o.oo	$o.oo	$ooo	$o.oo	$o.oo	$o.oo	$o.oo	$ooo	0%
28
Services

Cell Formulas
Range	Formula
W2:W27	W2	=LET(client,XLOOKUP(C2,ClientContracts!GJ$5:GJ$100,ClientContracts!GJ$5:GJ$100,"NCC"),result,FILTER(ClientContracts!CL$5:GJ$100,ClientContracts!GJ$5:GJ$100=client,"NCC"),found,FILTER(result,result=F2,"Not on Contract"),final,IF(result="NCC","NCC",IF(found="Not on Contract",found,"")),UNIQUE(TRANSPOSE(final)))

Book1
	FT	FU	FV	FW	FX	FY	FZ	GA	GB	GC	GD	GE	GF	GG	GH	GI	GJ	GK	GL
1
2										Short Term Accommodation} 24 hr	Short Term Accommodation] 24	Short Term Accommodation} 24 hr	$ Short	$ Short	$ Shori	OSS Version	Client ID.	Cost
3										Rates/Host Respite	hr Rates/Host Respite	Rates/Host Respite	Term Accommoda	Term Accomm	Term Accomm			Income ratio
4
5										.Short Term Accom (Award 24 Hrs)	0 Select support type if applicable		$16,057.67		0 Version211.		1446	57%
6										Select support type if applicable	Select support type if applicable Select support type if applicable				0 Version 2.9		1640	67%
7										Select support type if applicable	Selectsupportwpe if applicable Select support type if applicable				0 Version 2.10.		1447	63%
8	.Sleepover Penalty (Clamed)									Set (STA) Service Type Here	0 Selectsupport type if applicable		$		0 Version 2.11.		1268	65%
9										Select support type if applicable	Select support type if applicable Select support type if applicable		$		0 Version 2.6		1630	62%
10										Select support type if applicable	Select support type if applicable Select support type if applicable				0 Version 2.9		1529	65%
11										Set (STA) Service Type Here	0 Select support type if applicable				0 Version 2.11.		1281	45%
12										Select support type if applicable	Select support type if applicable Selectsupport type if applicable				0 Version 2.1		1442	61%
13										Select support type if applicable	Select support type if applicable Select support type if applicable		$		0 Version 210.		1596	47%
14										Select support type if applicable	Select support type if applicable Select support type if applicable				0 Version 2.8		1591	68%
15										.Short Term Accom (Award 24 Hrs)	0 Select support type if applicable		$ 1,000.00		0 Version 2.11.		1465	58%
16										Set (STA) Service Type Here	0 Selectsupport type if applicable		$		0 Version 2.11.		1641	58%
17										.SupportCoresident (020)	Select support type if applicable .Support Coresident (020)		$		0 Version 2.1		273	65%
18										.SupportCoresident (020)	Select support type if applicable .Support Coresident (Daily Activiw)				00Version 2.1		274	58%
19				.Support Worker (Daily Activlty)						.Host (020)	Select support type if applicable Selectsupport type if applicable		$		0 Version 2.1		1149	67%
20										Set (STA) Service Type Here	0 Selectsupport type if applicable		$		0 Version211.		1524	59%
21										.Selectsupport type if applicable	Select support type if applicable Select support type if applicable				00Version 2.10.		1475	66%
22										.Host (020)	Selectsupportwpe if applicable Select support type if applicable				0 Version 2.2		1636	#N/A
23										.Support Worker (020) 24hr Shift	Select support type if applicable Select support type if applicable		$		0 Version 2.9		289	61%
24										.Select support type if applicable	Select support type if applicable Select support type if applicable				00Version 2.10.		1633	71%
25
ClientContracts

=LET(client,XLOOKUP(C2,ClientContracts!GJ$5:GJ$100,ClientContracts!GJ$5:GJ$100,"NCC"),result,FILTER(ClientContracts!CL$5:GJ$100,ClientContracts!GJ$5:GJ$100=client,"NCC"),found,FILTER(result,result=F2,"Not on Contract"),final,IF(result="NCC","NCC",IF(found="Not on Contract",found,"")),UNIQUE(TRANSPOSE(final)))

The formula is broken into a few parts by using the LET() function for explanation : You don't see the interim results being generated and used, but heres whats happening.

LET(client,XLOOKUP(C2,ClientContracts!GJ$5:GJ$100,ClientContracts!GJ$5:GJ$100,"NCC")

So "client" here will have the result of the search of client id from C2, from ClientContracts sheet. So the result should normally be the "client_id". If id not found, client = "NCC"

result,FILTER(ClientContracts!CL$5:GJ$100,ClientContracts!GJ$5:GJ$100=client,"NCC")

"result" will contain the correct client_id row of serviceTypes on contract, or "NCC" if client row not found. (my data on ClientContracts starts row5, and I end on 100 - you may have more clients or less..)

found,FILTER(result,result=F2,"Not on Contract")

"found" will filter the "result" data looking for the ServiceType from F2. If the serviceType is not found (ie. it is not on contract), then "found"="Not on Contract". Otherwise "found" contains serviceType name.

final,IF(result="NCC","NCC",IF(found="Not on Contract",found,"")),UNIQUE(TRANSPOSE(final)))

"final" is actually what will be shown in Col "W" as your result. So If result="NCC", then is displays "NCC" as first priority eg. no client exists.
otherwise result <>"NCC", so it checks status of "found" and displays "Not on Contract" if the servicetype is not in the list, or puts a "" blank in if it is in the list.
The unique(transpose(final)) bit on the end was to clean up a result that spilled across the page.

Hope it helps, sorry its a bit wieldly, but I couldn't find another way to do it. Ensure your data is in correct columns for formula to work, or adjust formula to your columns...
Rob