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

J

jameskbw

New Member
Joined
Feb 8, 2023
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.
2023-02-08 11_28_05-Contacts and Accounts contact role-2023-02-07-15-12-18.xlsx - Excel.png
 
Sort by date Sort by votes
awoohaw said:
That will give you all the records without Statement Delivery. To then filter for a specific subset, filter another column, Account ID for example.
Click to expand...
I don't think that will work. It will filter out contacts with that specific value, but the accounts left behind still have a contact with that statement delivery value. So, the account still shows up / exists in the spreadsheet. I only need to keep (or somehow tag) accounts that do not have a single contact with the Statement Delivery value. Unless I'm just not understanding, I don't think this method will work.
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
okay. so first Identify all Account IDs with a Contact Role of "Statement Delivery" Then return all records with those Account IDs?
 
Upvote 0
Did you try the macro I suggested in Post #9?
 
Upvote 0
@jameskbw ,
It took me a while, but I think I have it figured out. Im happy you have 365, as this is only in that (and maybe 2021 excel).
but take a look. it is a wide mini workbook. I did not name the "named range" identified at the bottom, I guess excel does that on it its own. it is an Let function with Filter array formulas Even if this doesnt work for you, I've learned with this!
Let me know and I'll crunch some more if I need to.

Mr Excel Questions 3.xlsm
ABCDEFG
1Contact IDContact RoleAccount IDStatement DeliveryContact IDContact RoleAccount ID
2003DJ00000dVZxNYAWBilling; Statement Delivery; Technical; Non-Operational; Operations0014y01102ZsOcA003DJ00000dVZxOYAWNon-Operational; International Compliance0014y02202RDeA7
3003DJ00000dVZxOYAWNon-Operational; International Compliance0014y02202RDeA7003DJ00000dVZxPYAWNon-Operational0014y02202RDeA7
4003DJ00000dVZxPYAWNon-Operational0014y02202RDeA7003DJ00000dVZxQYAWNon-Operational0014y02202RDeA7
5003DJ00000dVZxQYAWNon-Operational0014y02202RDeA7003DJ00000dVZxRYAWNon-Operational0014y02202RDeA7
6003DJ00000dVZxRYAWNon-Operational0014y02202RDeA7003DJ00000dVZxSYAWNon-Operational0014y02202RDeA7
7003DJ00000dVZxSYAWNon-Operational0014y02202RDeA7003DJ00000dVZxYYAWNon-Operational0014y02202RDeA7
8003DJ00000dVZxYYAWNon-Operational0014y02202RDeA7003DJ00000dVZxxYAGNon-Operational0014y02202RDeDS
9003DJ00000dVZxxYAGNon-Operational0014y02202RDeDS003DJ00000dVZxZYAWNon-Operational0014y02202RDeA7
10003DJ00000dVZxZYAWNon-Operational0014y02202RDeA7003DJ00000dVZxaYAGNon-Operational0014y02202RDeA7
11003DJ00000dVZxaYAGNon-Operational0014y02202RDeA7003DJ00000dVZxbYAGNon-Operational0014y02202RDeA7
12003DJ00000dVZxbYAGNon-Operational0014y02202RDeA7003DJ00000dVZxcYAGNon-Operational0014y02202RDeA7
13003DJ00000dVZxcYAGNon-Operational0014y02202RDeA7003DJ00000dVZxdYAGNon-Operational0014y02202RDeA7
14003DJ00000dVZxdYAGNon-Operational0014y02202RDeA7003DJ00000dVZxeYAGNon-Operational0014y02202RDeA7
15003DJ00000dVZxeYAGNon-Operational0014y02202RDeA7003DJ00000dVZxfYAGNon-Operational0014y02202RDeA7
16003DJ00000dVZxfYAGNon-Operational0014y02202RDeA7003DJ00000dVZxgYAGNon-Operational0014y02202RDeA7
17003DJ00000dVZxgYAGNon-Operational0014y02202RDeA7003DJ00000dVZxhYAGNon-Operational0014y02202RDeA7
18003DJ00000dVZxhYAGNon-Operational0014y02202RDeA7003DJ00000dVZxiYAGNon-Operational; International Compliance0014y02202RDeA7
19003DJ00000dVZxiYAGNon-Operational; International Compliance0014y02202RDeA7003DJ00000dVZxjYAGNon-Operational0014y02202RDeA7
20003DJ00000dVZxjYAGNon-Operational0014y02202RDeA7003DJ00000dVZxkYAGNon-Operational0014y02202RDeA7
21003DJ00000dVZxkYAGNon-Operational0014y02202RDeA7003DJ00000dVZxlYAGBilling0014y02202RDeA7
22003DJ00000dVZxlYAGBilling0014y02202RDeA7003DJ00000dVZxmYAGNon-Operational0014y02202RDeA7
23003DJ00000dVZxmYAGNon-Operational0014y02202RDeA7003DJ00000dVZxvYAGNon-Operational0014y01102RDeDR
24003DJ00000dVZxnYAGBilling; Statement Delivery0016001100omi13003DJ00000dVZxwYAGNon-Operational0014y01102RDeDR
25003DJ00000dVZxoYAGNon-Operational0016001100omi13003DJ00000dVZxyYAGNon-Operational0014y02202RDeDS
26003DJ00000dVZxpYAGNon-Operational0016001100omi13003DJ00000dVZxzYAGNon-Operational0014y02202RDeDS
27003DJ00000dVZxqYAGNon-Operational0016001100omi13003DJ00000dVZy0YAGNon-Operational0014y02202RDeDS
28003DJ00000dVZxrYAGNon-Operational0016001100omi13
29003DJ00000dVZxsYAGNon-Operational0016001100omi13
30003DJ00000dVZxtYAGNon-Operational0016001100omi13
31003DJ00000dVZxuYAGNon-Operational0016001100omi13
32003DJ00000dVZxvYAGNon-Operational0014y01102RDeDR
33003DJ00000dVZxwYAGNon-Operational0014y01102RDeDR
34003DJ00000dVZxyYAGNon-Operational0014y02202RDeDS
35003DJ00000dVZxzYAGNon-Operational0014y02202RDeDS
36003DJ00000dVZy0YAGNon-Operational0014y02202RDeDS
37
38
39
Does Not Contain Filter
Cell Formulas
RangeFormula
E1:G27E1= LET( header,$A$1:$C$1, data,$A$2:$C$36, lfs,$D$1, cr,$B$2:$B$36, ai,$C$2:$C$36, excludes,FILTER(ai,--(ISNUMBER(FIND(lfs,cr))=TRUE),""), includes,(--(ISNUMBER(XMATCH(ai,excludes,0))=FALSE)), body,FILTER(data,includes,""), VSTACK(header,body))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
'Does Not Contain Filter'!_FilterDatabase='Does Not Contain Filter'!$A$1:$C$36E1
 
Upvote 0
awoohaw said:
@jameskbw ,
It took me a while, but I think I have it figured out. Im happy you have 365, as this is only in that (and maybe 2021 excel).
but take a look. it is a wide mini workbook. I did not name the "named range" identified at the bottom, I guess excel does that on it its own. it is an Let function with Filter array formulas Even if this doesnt work for you, I've learned with this!
Let me know and I'll crunch some more if I need to.

Mr Excel Questions 3.xlsm
ABCDEFG
1Contact IDContact RoleAccount IDStatement DeliveryContact IDContact RoleAccount ID
2003DJ00000dVZxNYAWBilling; Statement Delivery; Technical; Non-Operational; Operations0014y01102ZsOcA003DJ00000dVZxOYAWNon-Operational; International Compliance0014y02202RDeA7
3003DJ00000dVZxOYAWNon-Operational; International Compliance0014y02202RDeA7003DJ00000dVZxPYAWNon-Operational0014y02202RDeA7
4003DJ00000dVZxPYAWNon-Operational0014y02202RDeA7003DJ00000dVZxQYAWNon-Operational0014y02202RDeA7
5003DJ00000dVZxQYAWNon-Operational0014y02202RDeA7003DJ00000dVZxRYAWNon-Operational0014y02202RDeA7
6003DJ00000dVZxRYAWNon-Operational0014y02202RDeA7003DJ00000dVZxSYAWNon-Operational0014y02202RDeA7
7003DJ00000dVZxSYAWNon-Operational0014y02202RDeA7003DJ00000dVZxYYAWNon-Operational0014y02202RDeA7
8003DJ00000dVZxYYAWNon-Operational0014y02202RDeA7003DJ00000dVZxxYAGNon-Operational0014y02202RDeDS
9003DJ00000dVZxxYAGNon-Operational0014y02202RDeDS003DJ00000dVZxZYAWNon-Operational0014y02202RDeA7
10003DJ00000dVZxZYAWNon-Operational0014y02202RDeA7003DJ00000dVZxaYAGNon-Operational0014y02202RDeA7
11003DJ00000dVZxaYAGNon-Operational0014y02202RDeA7003DJ00000dVZxbYAGNon-Operational0014y02202RDeA7
12003DJ00000dVZxbYAGNon-Operational0014y02202RDeA7003DJ00000dVZxcYAGNon-Operational0014y02202RDeA7
13003DJ00000dVZxcYAGNon-Operational0014y02202RDeA7003DJ00000dVZxdYAGNon-Operational0014y02202RDeA7
14003DJ00000dVZxdYAGNon-Operational0014y02202RDeA7003DJ00000dVZxeYAGNon-Operational0014y02202RDeA7
15003DJ00000dVZxeYAGNon-Operational0014y02202RDeA7003DJ00000dVZxfYAGNon-Operational0014y02202RDeA7
16003DJ00000dVZxfYAGNon-Operational0014y02202RDeA7003DJ00000dVZxgYAGNon-Operational0014y02202RDeA7
17003DJ00000dVZxgYAGNon-Operational0014y02202RDeA7003DJ00000dVZxhYAGNon-Operational0014y02202RDeA7
18003DJ00000dVZxhYAGNon-Operational0014y02202RDeA7003DJ00000dVZxiYAGNon-Operational; International Compliance0014y02202RDeA7
19003DJ00000dVZxiYAGNon-Operational; International Compliance0014y02202RDeA7003DJ00000dVZxjYAGNon-Operational0014y02202RDeA7
20003DJ00000dVZxjYAGNon-Operational0014y02202RDeA7003DJ00000dVZxkYAGNon-Operational0014y02202RDeA7
21003DJ00000dVZxkYAGNon-Operational0014y02202RDeA7003DJ00000dVZxlYAGBilling0014y02202RDeA7
22003DJ00000dVZxlYAGBilling0014y02202RDeA7003DJ00000dVZxmYAGNon-Operational0014y02202RDeA7
23003DJ00000dVZxmYAGNon-Operational0014y02202RDeA7003DJ00000dVZxvYAGNon-Operational0014y01102RDeDR
24003DJ00000dVZxnYAGBilling; Statement Delivery0016001100omi13003DJ00000dVZxwYAGNon-Operational0014y01102RDeDR
25003DJ00000dVZxoYAGNon-Operational0016001100omi13003DJ00000dVZxyYAGNon-Operational0014y02202RDeDS
26003DJ00000dVZxpYAGNon-Operational0016001100omi13003DJ00000dVZxzYAGNon-Operational0014y02202RDeDS
27003DJ00000dVZxqYAGNon-Operational0016001100omi13003DJ00000dVZy0YAGNon-Operational0014y02202RDeDS
28003DJ00000dVZxrYAGNon-Operational0016001100omi13
29003DJ00000dVZxsYAGNon-Operational0016001100omi13
30003DJ00000dVZxtYAGNon-Operational0016001100omi13
31003DJ00000dVZxuYAGNon-Operational0016001100omi13
32003DJ00000dVZxvYAGNon-Operational0014y01102RDeDR
33003DJ00000dVZxwYAGNon-Operational0014y01102RDeDR
34003DJ00000dVZxyYAGNon-Operational0014y02202RDeDS
35003DJ00000dVZxzYAGNon-Operational0014y02202RDeDS
36003DJ00000dVZy0YAGNon-Operational0014y02202RDeDS
37
38
39
Does Not Contain Filter
Cell Formulas
RangeFormula
E1:G27E1= LET( header,$A$1:$C$1, data,$A$2:$C$36, lfs,$D$1, cr,$B$2:$B$36, ai,$C$2:$C$36, excludes,FILTER(ai,--(ISNUMBER(FIND(lfs,cr))=TRUE),""), includes,(--(ISNUMBER(XMATCH(ai,excludes,0))=FALSE)), body,FILTER(data,includes,""), VSTACK(header,body))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
'Does Not Contain Filter'!_FilterDatabase='Does Not Contain Filter'!$A$1:$C$36E1
Click to expand...

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.
 
Upvote 0
mumps said:
Did you try the macro I suggested in Post #9?
Click to expand...
Hi, I had to lookup how to use VBA code and macros. I believe I ran it successfully, but it seemed to highlight both accounts with Statement Delivery and without. E.G. the red highlight was on accounts that had statement delivery and ones that did not.
 
Upvote 0
awoohaw said:
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.
Click to expand...
I will look at this not and try to implement. Thank you.
 
Upvote 0
I tested the macro on the data you posted and it worked properly. It highlighted cell C2 and the range C24:C31. Did you try the macro on the same data or in a different file?
 
Upvote 0
mumps said:
I tested the macro on the data you posted and it worked properly. It highlighted cell C2 and the range C24:C31. Did you try the macro on the same data or in a different file?
Click to expand...
Different sheet, but column name and order was the same.
 
Upvote 0
Please post a screenshot (not picture) of that sheet.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
VBA: Autofilter containing specific cell value from another Worksheet
Replies
1
Views
538
Fanalis
F
D
  • Solved
Textjoin with conditions - cell contains specific text
2
Replies
13
Views
3K
Fluff
Fluff
C
  • Solved
Updating a specific cell value in an autofiltered table
Replies
3
Views
486
skillilea
S
F
  • Solved
Copying and pasting onto mastersheet but files have different formats (need if statement help)
2
Replies
12
Views
1K
NateSC
NateSC
A
  • Question Question
Help on how to update this code to have it send to recipients in Cell Value
Replies
1
Views
1K
agutosay
A

Forum statistics

Threads
1,223,885
Messages
6,175,181
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top