Conditional Formatting Partial Match

n3sky

New Member
Joined
Oct 24, 2011
Messages
4
I apologize in advance if this is already posted somewhere - I searched the forum for about 30 minutes and wasn't able to find the answer.

I'm trying to find a formula that will ignore text when searching for exact matches contained within the same column. For example, the column contains the following:

WS-X4606-X2-E
WS-X4606-X2-E=
WS-X4606-X2-E=

The = should be ignored and these should all be highlighted as an exact match.

Any help would be greatly appreciated!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
I apologize in advance if this is already posted somewhere - I searched the forum for about 30 minutes and wasn't able to find the answer.

I'm trying to find a formula that will ignore text when searching for exact matches contained within the same column. For example, the column contains the following:

WS-X4606-X2-E
WS-X4606-X2-E=
WS-X4606-X2-E=

The = should be ignored and these should all be highlighted as an exact match.

Any help would be greatly appreciated!
Maybe something like this...

Conditional Formatting
Use a Formula...

=SEARCH("WS-X4606-X2-E",A1)
 

n3sky

New Member
Joined
Oct 24, 2011
Messages
4
Thanks for the reply. Unfortunately that won't work since there are lots of different text possibilities. Here's more of the spreadsheet:


WS-X4612-SFP-E
WS-X4612-SFP-E=
WS-X4624-SFP-E
WS-X4624-SFP-E=
WS-X4648-RJ45-E
WS-X4648-RJ45-E=

The formula should highlight all of these as duplicates. WS-X4612-SFP-E matches WS-X4612-SFP-E= (without the equal sign) and so on.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Thanks for the reply. Unfortunately that won't work since there are lots of different text possibilities. Here's more of the spreadsheet:


WS-X4612-SFP-E
WS-X4612-SFP-E=
WS-X4624-SFP-E
WS-X4624-SFP-E=
WS-X4648-RJ45-E
WS-X4648-RJ45-E=

The formula should highlight all of these as duplicates. WS-X4612-SFP-E matches WS-X4612-SFP-E= (without the equal sign) and so on.
The = sign on the end...

Is that the only "odd" character that you want to ignore?
 

n3sky

New Member
Joined
Oct 24, 2011
Messages
4

ADVERTISEMENT

Yes, the = is the only character that needs to be ignored.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Yes, the = is the only character that needs to be ignored.
You can use a formula like this:

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:140px;" /><col style="width:52px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style=" border-style:solid; border-width:1px; border-color:#000000; ">WS-X4612-SFP-E</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRUE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">WS-X4612-SFP-E=</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRUE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">WS-X4624-SFP-E</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRUE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">WS-X4624-SFP-E=</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRUE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">WS-X4648-RJ45-E</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRUE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">WS-X4648-RJ45-E=</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRUE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">WS-X4648-RJ49-E=</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">FALSE</td></tr></table> <br /><br />
=SUMPRODUCT(--(SUBSTITUTE(A$2:A$8,"=","")=SUBSTITUTE(A2,"=","")))>1
 

n3sky

New Member
Joined
Oct 24, 2011
Messages
4
Thank you! This is very close to working. The only problem is for some reason it's one cell off. Here's an example:

ASA5550-BUN-K9
C2960S-STACK
C2960S-STACK=
C3KX-NM-10G
C3KX-NM-10G=
C3KX-NM-1G
C3KX-NM-1G=

It's highlighting all but C3KX-NM-1G= as having a match, even though there is no other ASA5550-BUN-K9, and there is a non-equal sign match for C3KX-NM-1G=. Any idea why that would be occurring? Also, is it possible to have the formula ignore blank cells?

Thanks again!
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Thank you! This is very close to working. The only problem is for some reason it's one cell off. Here's an example:

ASA5550-BUN-K9
C2960S-STACK
C2960S-STACK=
C3KX-NM-10G
C3KX-NM-10G=
C3KX-NM-1G
C3KX-NM-1G=

It's highlighting all but C3KX-NM-1G= as having a match, even though there is no other ASA5550-BUN-K9, and there is a non-equal sign match for C3KX-NM-1G=. Any idea why that would be occurring? Also, is it possible to have the formula ignore blank cells?

Thanks again!
This works for me...

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:123px;" /><col style="width:72px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style=" border-style:solid; border-width:1px; border-color:#000000; ">ASA5550-BUN-K9</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">FALSE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; "> </td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">FALSE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">C2960S-STACK</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRUE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">C2960S-STACK=</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRUE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">C3KX-NM-10G</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRUE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">C3KX-NM-10G=</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRUE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">C3KX-NM-1G</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRUE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">C3KX-NM-1G=</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRUE</td></tr></table> <br /><br />
=SUMPRODUCT(--(A$2:A$9<>""),--(SUBSTITUTE(A$2:A$9,"=","")=SUBSTITUTE(A2,"=","")))>1

Have you considered doing an Edit>Replace to remove those equal signs from the data altogether?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,446
Messages
5,642,201
Members
417,259
Latest member
gtacw

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
Top