Conditional Formatting Partial Match

[n3sky]

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!

 

[T. Valko]

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]

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]

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]

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

 

[T. Valko]

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

You can use a formula like this:

Book1
	A	B
2	WS-X4612-SFP-E	TRUE
3	WS-X4612-SFP-E=	TRUE
4	WS-X4624-SFP-E	TRUE
5	WS-X4624-SFP-E=	TRUE
6	WS-X4648-RJ45-E	TRUE
7	WS-X4648-RJ45-E=	TRUE
8	WS-X4648-RJ49-E=	FALSE
Sheet1

=SUMPRODUCT(--(SUBSTITUTE(A$2:A$8,"=","")=SUBSTITUTE(A2,"=","")))>1

 

[n3sky]

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]

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...

Book1
	A	B
2	ASA5550-BUN-K9	FALSE
3		FALSE
4	C2960S-STACK	TRUE
5	C2960S-STACK=	TRUE
6	C3KX-NM-10G	TRUE
7	C3KX-NM-10G=	TRUE
8	C3KX-NM-1G	TRUE
9	C3KX-NM-1G=	TRUE
Sheet1

=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?