Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Highlight (any) first text word that is a duplicate

Status
Not open for further replies.

jlr123

Technical User
Feb 24, 2014
117
0
16
US
I want to highlight all duplicates based on the first text of each cell in a column. There are many but this based on any duplicate first text word in the column not just a specific text.
E$5:E$132
 
Please provide this information:

1. This is what I have - example of your data.
2. This is what I want - how your data should be [highlight #FCE94F]highlighted[/highlight].

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Hi,

I had to add a helper column in column F

[pre]
E F

Mary 3
Skip 2
Mary 2
Skip 1
Mary 1
...
[/pre]
Column F has this formula...
[tt]
F5: =SUMPRODUCT(--(E5:E$9999=E5))
[/tt]
Using Conditional Formatting...
1. SELECT Column E
2. Open the CF Editor
3. Add a new rule using a formula to determine which cells to format
4. Use this formula...
=AND(SUMPRODUCT(--($E:$E=E1))=F1,F1>1)
5. Select your Format
6. Apply the Conditional Formatting rule

The reason I used e5:E$9999=E5 and $E:$E=E1 is in the event that your list of duplicates expands.

Tt-first_dup_CF_v94ied.png
[URL unfurl="true"]https://res.cloudinary.com/engineering-com/raw/upload/v1706809896/tips/tt-cf_first_duplicate_t1bg7z.xlsx[/url]

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top