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.

 

[mumps]

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Also, explain what you want to do when you find them.

 

[awoohaw]

A mini sample (anonmized) using the xl2bb add in would be helpful.
But does the autofilter work for partial text string searches:

I hope this works:

Mr Excel Questions2.xlsm
	I	J
2	Col1	Col2
3	YJO	HKE
4	ADQ	DLH
5	OLT	SEL
6	VKV	ORL
7	BUE	IXN
8	NVI	GEE
9	JOD	NGD
10	TAP	PTE
11	XLG	UFF
12	VZC	ADV
13
14	Filter Rule: Does Not Contain "V"
15	Col1	Col2
16	YJO	HKE
17	ADQ	DLH
18	OLT	SEL
20	BUE	IXN
22	JOD	NGD
23	TAP	PTE
24	XLG	UFF
CondFmt HiLite Cells within Rng

 

[jameskbw]

Sure, I will work on anonymizing the data, uploading a mini-sheet, and clarifying what I need.

 

[awoohaw]

Sure, I will work on anonymizing the data, uploading a mini-sheet, and clarifying what I need.

You only need a few rows
But, have you tried the filter? Or Have I missed something different you needed?

 

[jameskbw]

Okay, let me know if this pastes correctly:

Contacts and Accounts contact role-2023-02-07-15-12-18.xlsx
	A	B	C
1	Contact ID	Contact Role	Account ID
2	003DJ00000dVZxNYAW	Billing; Statement Delivery; Technical; Non-Operational; Operations	0014y01102ZsOcA
3	003DJ00000dVZxOYAW	Non-Operational; International Compliance	0014y02202RDeA7
4	003DJ00000dVZxPYAW	Non-Operational	0014y02202RDeA7
5	003DJ00000dVZxQYAW	Non-Operational	0014y02202RDeA7
6	003DJ00000dVZxRYAW	Non-Operational	0014y02202RDeA7
7	003DJ00000dVZxSYAW	Non-Operational	0014y02202RDeA7
8	003DJ00000dVZxTYAW	Non-Operational	0014y02202RDeA7
9	003DJ00000dVZxUYAW	Non-Operational	0014y02202RDeA7
10	003DJ00000dVZxVYAW	Non-Operational	0014y02202RDeA7
11	003DJ00000dVZxWYAW	Non-Operational	0014y02202RDeA7
12	003DJ00000dVZxXYAW	Non-Operational	0014y02202RDeA7
13	003DJ00000dVZxYYAW	Non-Operational	0014y02202RDeA7
14	003DJ00000dVZxZYAW	Non-Operational	0014y02202RDeA7
15	003DJ00000dVZxaYAG	Non-Operational	0014y02202RDeA7
16	003DJ00000dVZxbYAG	Non-Operational	0014y02202RDeA7
17	003DJ00000dVZxcYAG	Non-Operational	0014y02202RDeA7
18	003DJ00000dVZxdYAG	Non-Operational	0014y02202RDeA7
19	003DJ00000dVZxeYAG	Non-Operational	0014y02202RDeA7
20	003DJ00000dVZxfYAG	Non-Operational	0014y02202RDeA7
21	003DJ00000dVZxgYAG	Non-Operational	0014y02202RDeA7
22	003DJ00000dVZxhYAG	Non-Operational	0014y02202RDeA7
23	003DJ00000dVZxiYAG	Non-Operational; International Compliance	0014y02202RDeA7
24	003DJ00000dVZxjYAG	Non-Operational	0014y02202RDeA7
25	003DJ00000dVZxkYAG	Non-Operational	0014y02202RDeA7
26	003DJ00000dVZxlYAG	Billing	0014y02202RDeA7
27	003DJ00000dVZxmYAG	Non-Operational	0014y02202RDeA7
28	003DJ00000dVZxnYAG	Billing; Statement Delivery	0016001100omi13
29	003DJ00000dVZxoYAG	Non-Operational	0016001100omi13
30	003DJ00000dVZxpYAG	Non-Operational	0016001100omi13
31	003DJ00000dVZxqYAG	Non-Operational	0016001100omi13
32	003DJ00000dVZxrYAG	Non-Operational	0016001100omi13
33	003DJ00000dVZxsYAG	Non-Operational	0016001100omi13
34	003DJ00000dVZxtYAG	Non-Operational	0016001100omi13
35	003DJ00000dVZxuYAG	Non-Operational	0016001100omi13
36	003DJ00000dVZxvYAG	Non-Operational	0014y01102RDeDR
37	003DJ00000dVZxwYAG	Non-Operational	0014y01102RDeDR
38	003DJ00000dVZxxYAG	Non-Operational	0014y02202RDeDS
39	003DJ00000dVZxyYAG	Non-Operational	0014y02202RDeDS
40	003DJ00000dVZxzYAG	Non-Operational	0014y02202RDeDS
41	003DJ00000dVZy0YAG	Non-Operational	0014y02202RDeDS
Sheet1

What I'm trying to do is treat all contact id's with the same account id as one group. Within that group, look at contact role (columb B). Within the same account id (column C) if no contact has string "Statement Delivery" in column B, flag that in a new column. I'm not sure the best way to do that, but true, false, 1, 0 would be fine. I just need an easy way to look at the spreadsheet and show all the account id's that don't have any statement delivery value for all rows with the same account id.

Let me know if that makes sense, happy to clarify or provide more info.

 

[awoohaw]

Use the date filter. Select all of your data. From Ribbon, select Data, then click the Filter Icon (looks a little like a funnel).
THen little arrows will appear.
Then select the column you want to filter on. You'll get these dialogs:

 

[awoohaw]

That will give you all the records without Statement Delivery. To then filter for a specific subset, filter another column, Account ID for example.

 

[mumps]

Try:

Sub FlagRows()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, fnd As Range
    v = Range("C2", Range("C" & Rows.Count).End(xlUp)).Value
    With CreateObject("scripting.dictionary")
        For i = LBound(v) To UBound(v)
            If Not .exists(v(i, 1)) Then
                .Add v(i, 1), Nothing
                With ActiveSheet
                    .Range("A1").CurrentRegion.AutoFilter 3, v(i, 1)
                    Set fnd = .AutoFilter.Range.Offset(1).Columns(2).Find("Statement Delivery", LookIn:=xlValues, lookat:=xlPart)
                    If Not fnd Is Nothing Then
                        .AutoFilter.Range.Offset(1).Columns(3).Interior.ColorIndex = 3
                    End If
                End With
            End If
        Next i
    End With
    Range("A1").AutoFilter
    Rows(Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1).Delete
    Application.ScreenUpdating = True
End Sub

 

[awoohaw]

Here it is with two filters (the 'does not contain = 'Statement Delivery' and for a selected Account ID)

Mr Excel Questions2.xlsm
	G	H	I	J	K
1		Contact ID	Contact Role	Account ID
36		003DJ00000dVZxvYAG	Non-Operational	0014y01102RDeDR
37		003DJ00000dVZxwYAG	Non-Operational	0014y01102RDeDR
42
Does Not Contain Filter