Conditional formatting to highlight cells containing a specific list of words

K

kdorClintR

New Member
Joined
Jul 7, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I get a spreadsheet every month full of data that then has to be sorted and sifted so that I can distribute the names of businesses to multiple agents depending on the county in which the business is located. I have 7 agents who cover 105 counties and normally have around 500 businesses that need to be distributed to the corresponding agent. Others have done this manually before, but this takes quite a bit of time sorting, copying, and pasting into new worksheets. Is there a way I can format the cells containing the counties by searching for multiple county names at the same time so that I can then sort the sheet by cell color and consolidate all of the counties
 

Attachments

  • Capture.PNG
    Capture.PNG
    248.1 KB · Views: 31

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
you can conditional format or apply the agent name based on a lookup table
Not sure i follow the example exactly in the image

Is it just the country the agents are split by or is it company name or some combination of both

from your a table of countries for each agent - then you could have 7 conditional rules to countif()=1 for that agent
OR even a lookup that looks up the country and then returns the agent name into a new column on the list

I have just done a couple of agents to give an example

Book1
ABCDEFG
1CountryAgent 1Agent 2agent 3Agent 4Agent 5Agent 6
2Country 1Country 3Country 1Country 10
3Country 2Country 7Country 2Country 11
4Country 3Country 12country 8Country 12
5Country 4Country 15country 23Country 13
6Country 5Country 14
7Country 6Country 15
8Country 7Country 18
9Country 8Country 21
10Country 9Country 22
11Country 10Country 24
12Country 11
13Country 12
14Country 13
15Country 14
16Country 15
17Country 16
18Country 17
19Country 18
20Country 19
21Country 20
22Country 21
23Country 22
24Country 23
25Country 24
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D1Expression=COUNTIF($D:$D,D1)=1textNO
D1Expression=COUNTIF($C:$C,D1)=1textNO
D1Expression=COUNTIF($B:$B,D1)=1textNO
C1Expression=COUNTIF($D:$D,C1)=1textNO
C1Expression=COUNTIF($C:$C,C1)=1textNO
C1Expression=COUNTIF($B:$B,C1)=1textNO
B1Expression=COUNTIF($D:$D,B1)=1textNO
B1Expression=COUNTIF($C:$C,B1)=1textNO
B1Expression=COUNTIF($B:$B,B1)=1textNO
A:AExpression=COUNTIF($D:$D,A1)=1textNO
A:AExpression=COUNTIF($C:$C,A1)=1textNO
A:AExpression=COUNTIF($B:$B,A1)=1textNO
 
Upvote 0
Welcome to the MrExcel board!

Instead of dealing with a lot of conditional formatting rules, filtering by colour etc, you might consider using another column like this then use AutoFilter to get your agent lists each time.

20 07 08.xlsm
IJKRSTUVWXY
1CountyAgentAgent 1Agent 2Agent 3Agent 4Agent 5Agent 6Agent 7
2County 1Agent 2County 3County 1County 10
3County 2Agent 2County 7County 2County 11
4County 3Agent 1County 12County 8County 12
5County 4No agentCounty 15County 23County 13
6County 5No agentCounty 14
7County 6No agentCounty 15
8County 7Agent 1County 18
9County 8Agent 2County 21
10County 9No agentCounty 22
11County 10Agent 3County 24
12County 11Agent 3
13County 12Agent 4
14County 13Agent 3
15County 14Agent 3
16County 15Agent 4
17County 16No agent
18County 17No agent
19County 18Agent 3
20County 19No agent
21County 20No agent
22County 21Agent 3
23County 22Agent 3
24County 23Agent 2
25County 24Agent 3
26
Agents
Cell Formulas
RangeFormula
K2:K25K2=IF(COUNTIF(S$2:Y$24,I2),INDEX(S$1:Y$1,SUMPRODUCT((S$2:Y$24=I2)*(COLUMN(S$2:Y$24)-COLUMN(S2)+1))),"No agent")
 
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

Instead of dealing with a lot of conditional formatting rules, filtering by colour etc, you might consider using another column like this then use AutoFilter to get your agent lists each time.

20 07 08.xlsm
IJKRSTUVWXY
1CountyAgentAgent 1Agent 2Agent 3Agent 4Agent 5Agent 6Agent 7
2County 1Agent 2County 3County 1County 10
3County 2Agent 2County 7County 2County 11
4County 3Agent 1County 12County 8County 12
5County 4No agentCounty 15County 23County 13
6County 5No agentCounty 14
7County 6No agentCounty 15
8County 7Agent 1County 18
9County 8Agent 2County 21
10County 9No agentCounty 22
11County 10Agent 3County 24
12County 11Agent 3
13County 12Agent 4
14County 13Agent 3
15County 14Agent 3
16County 15Agent 4
17County 16No agent
18County 17No agent
19County 18Agent 3
20County 19No agent
21County 20No agent
22County 21Agent 3
23County 22Agent 3
24County 23Agent 2
25County 24Agent 3
26
Agents
Cell Formulas
RangeFormula
K2:K25K2=IF(COUNTIF(S$2:Y$24,I2),INDEX(S$1:Y$1,SUMPRODUCT((S$2:Y$24=I2)*(COLUMN(S$2:Y$24)-COLUMN(S2)+1))),"No agent")
Click to expand...

I like the autofilter option better, so thanks for suggesting that. However, when I copied and pasted the formula you shared, it didn't work, and as I'm just good enough with Excel to grasp the basics, I'm not sure why. I pasted the formula into R1, then dragged the bottom right hand corner of the cell to apply it to all subsequent cells, but each cell only displayed "no agent". Should I have done something different?
Capture.PNG
 
Upvote 0
kdorClintR said:
it didn't work, .... I'm not sure why.
Click to expand...
It is very hard to tell from this end by just looking at a picture. The most likely culprits are ..
The data does't match exactly
The formula needs a slight tweak

To try to resolve, can you
  1. Copy the formula from your cell R1 and paste it in a reply here
  2. In a vacant cell on that sheet you have shown above put this formula
    =EXACT(I2,Y2)
    and report the result here.
    Both I2 and Y2 appear to show the same name "Allen" but if that formula returns FALSE then perhaps one of the names has a trailing or leading space.

If none of that helps, can you post those 23 rows of your sheet with XL2BB so that we can test with your exact data?
 
Upvote 0
Peter_SSs said:
To try to resolve, can you
  1. Copy the formula from your cell R1 and paste it in a reply here
  2. In a vacant cell on that sheet you have shown above put this formula
    =EXACT(I2,Y2)
    and report the result here.
    Both I2 and Y2 appear to show the same name "Allen" but if that formula returns FALSE then perhaps one of the names has a trailing or leading space.

If none of that helps, can you post those 23 rows of your sheet with XL2BB so that we can test with your exact data?
Click to expand...

There was a trailing space on the range of counties assigned to agents. I went through and deleted that space and the formula works perfectly now. Thank you so much for your help!
 
Upvote 0
Here with a small change to the formula provided by Peter for your consideration.

Question: Is it possible that in columns S to Y you have duplicate agents?

varios 09jul2020.xlsm
IRSTUVWXY
1CountyAgentAgent 1Agent 2Agent 3Agent 4Agent 5Agent 6Agent 7
2County 1Agent 2County 3County 1County 10
3County 2Agent 2County 7County 2County 11
4County 3Agent 1County 8County 12
5County 4No agentCounty 23County 13
6County 5No agentCounty 14
7County 6No agentCounty 15
8County 7Agent 1County 18
9County 8Agent 2County 21
10County 9No agentCounty 22
11County 10Agent 3County 24
12County 11Agent 3
13County 12Agent 3
14County 13Agent 3
15County 14Agent 3
16County 15Agent 3
17County 16No agent
18County 17No agent
19County 18Agent 3
20County 19No agent
21County 20No agent
22County 21Agent 3
23County 22Agent 3
24County 23Agent 2
25County 24Agent 3
Hoja4
Cell Formulas
RangeFormula
R2:R25R2=IFERROR(INDEX(S$1:Y$1,,SUMPRODUCT(($S$2:$Y$24=I2)*COLUMN(S$1:Y$1))-18),"No agent")
 
Last edited:
Upvote 0
DanteAmor said:
Question: Is it possible that in columns S to Y you have duplicate agents?
Click to expand...
Do you mean duplicate Counties?
BTW, your suggested formula will return incorrect results if any columns are subsequently added/deleted anywhere to the left of column S.

kdorClintR said:
There was a trailing space on the range of counties assigned to agents. I went through and deleted that space and the formula works perfectly now. Thank you so much for your help!
Click to expand...
You're welcome. Glad you got it sorted. :)
 
Upvote 0
DanteAmor said:
It is correct, I mean Counties ?
Click to expand...
It seems from the OP's response in post #6 that such an issue does not occur, but should it be possible then my suggestion for the OP would be either of these (simpler formulas than my previous one anyway, especially if every County has at least one agent then the column L formula should suffice. :))

20 07 10.xlsm
IJKLMNOPQRSTUVWXY
1CountyAgentAgentAgent 1Agent 2Agent 3Agent 4Agent 5Agent 6Agent 7
2County 1Agent 2Agent 2County 3County 1County 2
3County 2Agent 3, Agent 1Agent 3, Agent 1County 2County 12County 11
4County 3Agent 1Agent 1County 12County 8County 12
5County 4No agent County 15County 23County 13
6County 5No agent County 14
7County 6No agent County 15
8County 7No agent County 18
9County 8Agent 2Agent 2County 21
10County 9No agent County 22
11County 10No agent County 24
12County 11Agent 3Agent 3
13County 12Agent 2, Agent 1, Agent 3Agent 2, Agent 1, Agent 3
14County 13Agent 3Agent 3
15County 14Agent 3Agent 3
16County 15Agent 1, Agent 3Agent 1, Agent 3
17County 16No agent 
18County 17No agent 
19County 18Agent 3Agent 3
20County 19No agent 
21County 20No agent 
22County 21Agent 3Agent 3
23County 22Agent 3Agent 3
24County 23Agent 2Agent 2
25County 24Agent 3Agent 3
Agents (2)
Cell Formulas
RangeFormula
K2:K25K2=IF(COUNTIF(S$2:Y$25,I2),TEXTJOIN(", ",1,IF(S$1:Y$24=I2,S$1:Y$1,"")),"No agent")
L2:L25L2=TEXTJOIN(", ",1,IF(S$1:Y$24=I2,S$1:Y$1,""))
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Easiest way to get total.
Replies
3
Views
269
awoohaw
awoohaw
D
  • Question
Multiple conditions
Replies
8
Views
177
awoohaw
awoohaw
W
  • Question
VBA InputBox without entry quitting Sub/Macro
Replies
1
Views
109
Micron
M
M
  • Question
Error 91 when pasting into outlook from excel using vba
Replies
3
Views
507
Micron
M
R
  • Solved
Using conditional formatting to highlight the row of a cell containing specific text.
Replies
4
Views
268
etaf
etaf

Forum statistics

Threads
1,214,784
Messages
6,121,536
Members
449,037
Latest member
tmmotairi

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