Need to determine all rows with common ID value that don't contain a specific string value

[jameskbw]

I have data that represent a child parent relationship from Salesforce - account and contact. Every row contains an account id which would be the value to "group by."

Each row contains several contact specific fields, and also the account id. The goal is to find all accounts (or at the account level) where none of the contacts contain the string "Statement Delivery" in the Contact Role field / column. E.G. if there are 20 contacts that all have the same account id value, and none of them contain "Statement Delivery" in the contact role column, I want to flag or tag that account some how.

This is a multi select picklist field so strings are separated by ;

I'm not even sure where to start, but hoping someone can point me in the right direction. Any help would be greatly appreciated.

 

[jameskbw]

Please post a screenshot (not picture) of that sheet.

Here is a link to the file - if that's easier. Shared Ext
The data has been sanitized but concept is exactly the same.

 

[jameskbw]

the words/codes in the Let statment are:
header, the header row of column titles.
data, the entire data being sorted
lfs look for string (that you want to exclude)
cr, the contact role column
ai, account ID column
excludes, the records that need to be excluded (matched to the look for string)
includes, the records that do not match the look for string
body, is the data after it is filtered.

Thank you for the explanation. I tried to apply to my larger dataset and I couldn't get it to work. I think this problem may just be over my basic excel skills, even with help.

 

[awoohaw]

ah,we will get it. I'll try to configure that statement to your column letters, but you'll need to adjust your row numbers.

What column is contact ID in? It is not visible in your pictures. It is only in the miniworkbook with columns A,B,C.

 

[jameskbw]

I appreciate all the help. I was able to figure out a more straight forward solution and I'll post in case it's useful to anyone else.

Basic steps were:

1. Get list of all unique account id's in new worksheet.
2. Use the following formula to search for "Statement Delivery" in Contact Role Column and return 1 if found, 0 otherwise.
   1. =IF(ISNUMBER(SEARCH("Statement Delivery",B2)),1,0)
3. In the new sheet with unique accounts, use formula to sum all values using the account as the key.
   1. =SUMIF('Contacts and Accounts contact r'!C:C,Sheet1!A2,'Contacts and Accounts contact r'!D:D)

Contacts and Accounts contact role cleaned.xlsx
	A	B	C	D
1	contact	Contact Role	account
2	name001	Billing; Statement Delivery; Technical; Non-Operational; Operations	Account001	1
3	name002	Non-Operational; International Compliance	Account002	0
4	name003	Non-Operational	Account002	0
5	name004	Non-Operational	Account002	0
6	name005	Non-Operational	Account002	0
7	name006	Non-Operational	Account002	0
8	name007	Non-Operational	Account002	0
9	name008	Non-Operational	Account002	0
10	name009	Non-Operational	Account002	0
11	name010	Non-Operational	Account002	0
12	name011	Non-Operational	Account002	0
13	name012	Non-Operational	Account002	0
14	name013	Non-Operational	Account002	0
15	name014	Non-Operational	Account002	0
16	name015	Non-Operational	Account002	0
17	name016	Non-Operational	Account002	0
18	name017	Non-Operational	Account002	0
19	name018	Non-Operational	Account002	0
20	name019	Non-Operational	Account002	0
21	name020	Non-Operational	Account002	0
22	name021	Non-Operational	Account002	0
23	name022	Non-Operational; International Compliance	Account002	0
24	name023	Non-Operational	Account002	0
25	name024	Non-Operational	Account002	0
26	name025	Billing	Account002	0
27	name026	Non-Operational	Account002	0
28	name027	Billing	Account003	0
29	name028	Non-Operational	Account003	0
30	name029	Non-Operational	Account003	0
31	name030	Non-Operational	Account003	0
32	name031	Non-Operational	Account003	0
33	name032	Non-Operational	Account003	0
34	name033	Non-Operational	Account003	0
35	name034	Non-Operational	Account003	0
36	name035	Non-Operational	Account004	0
37	name036	Non-Operational	Account004	0
38	name037	Non-Operational	Account005	0
39	name038	Non-Operational	Account005	0
40	name039	Non-Operational	Account005	0
41	name040	Non-Operational	Account005	0
42	name041	Non-Operational	Account005	0
43	name042	Non-Operational	Account005	0
44	name043	Non-Operational	Account006	0
45	name044	Non-Operational	Account007	0
46	name045	Non-Operational	Account007	0
47	name046	Non-Operational	Account007	0
48	name047	Non-Operational	Account007	0
49	name048	Non-Operational	Account007	0
50	name049	Non-Operational	Account007	0
51	name050	Non-Operational	Account008	0
52	name051	Billing; Statement Delivery	Account009	1
53	name052	Non-Operational	Account010	0
54	name053	Non-Operational	Account010	0
55	name054	Non-Operational	Account010	0
56	name055	Non-Operational	Account010	0
57	name056	Non-Operational	Account010	0
58	name057	Non-Operational	Account011	0
Contacts and Accounts contact r

Cell Formulas
Range	Formula
D2:D58	D2	=IF(ISNUMBER(SEARCH("Statement Delivery",B2)),1,0)

Contacts and Accounts contact role cleaned.xlsx
	A	B
1	account
2	Account001	1
3	Account002	0
4	Account003	0
5	Account004	0
6	Account005	0
7	Account006	0
8	Account007	0
9	Account008	0
10	Account009	1
11	Account010	0
12	Account011	0
13	Account012	0
14	Account013	0
15	Account014	0
16	Account015	0
17	Account016	0
18	Account017	1
Sheet1

Cell Formulas
Range	Formula
B2:B18	B2	=SUMIF('Contacts and Accounts contact r'!C:C,Sheet1!A2,'Contacts and Accounts contact r'!D:D)