Duplication Condition Format Formula

[Newbienew]

Hello All,

I have a condition formate that allows me to highlight names in to separate columns. but I need the formula to exclude characters buy still highlight them as duplicates if that makes sense.
So some names have ( ' ) or a space or even ( - ). But I am looking for the formula to count it as a duplicate.

James	o'brien
James	obrien
Jose	Arroz-Ortega
Jose	Arroz Ortega
Jose	Arroz-Ortega

The formula that i am using is =COUNTIFS($F$1:$F$96739,$F1,$G$1:$G$96739,$G1)>1

I also use a modified verison to detect duplicates on other sheets as well. I have provided a link as I am not able to use XL2BB. It crashes every time i use it. BLANK WORKBOOK.xlsb

 

[Snakehips]

Does the below help?
I would suggest that a pair of 'helper columns', which could be hidden, would be the way to go if you can.
Otherwise you could use a single Sumproduct formula but it would be a bit unwieldy.

Book1
	F	G	H	I	J
2	James	o'--- brien	3	James	obrien
3	James	obrien	3	James	obrien
4	Fred	o brien	1	Fred	obrien
5	Jame's	obrien	3	James	obrien
6	James	onion	1	James	onion
Sheet10

Cell Formulas
Range	Formula
H2:H6	H2	=COUNTIFS($I$2:$I$100,I2,$J$2:J$100,J2)
I2:J6	I2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2,"'",""),"-"," ")," ","")

 

[Newbienew]

I think i am lost on how your formula would work as a condition format. It would seem I would have two columns of names I think. Is this about right from what iam following

 

[Snakehips]

I think i am lost on how your formula would work as a condition format. It would seem I would have two columns of names I think. Is this about right from what iam following

I'm not surprised that you are lost! My apologies, I totally lost sight of the fact that this was related to conditional formatting.

However maybe the below will make more sense.

Book1
	F	G
2	James	o'--- brien
3	James	obrien
4	Fred	o brien
5	Jame's	obrien
6	James	onion
7	jame	s o brien
Sheet10

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:G7	Expression	=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($G$2:$G$11&$F$2:$F$11,"'",""),"-","")," ","") =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($G2&$F2,"'",""),"-","")," ","")))>1	text	NO

Obviously, you will need to tweak the range references.

 

[Newbienew]

I attempted to try it but this is what i got for my results

LAST NAME (0)	FIRST NAME (0)
LEWIS	THOMAS
LEWIS	THOMAS
JONES	KELLY

 

[Snakehips]

What columns and range of rows for the names, excluding the header row?

 

[Newbienew]

The Columns are F and G. This is what i am currently using.

=COUNTIFS($F$1:$F$96739,$F1,$G$1:$G$96739,$G1)>1 (same sheet condition format)
=COUNTIFS(GAINS!$E$2:$E$8610,$F1,GAINS!$F$2:$F$8610,$G1)>0 (gains sheet condition format) this checks the top sheet and find duplicates on that sheet with this sheet.
=COUNTIFS('APP LOG'!$F$2:$F$13452,$F1,'APP LOG'!$G$2:$G$13452,$G1)>0 (app sheet condition format) this checks the top sheet and find duplicates on that sheet with this sheet.

 

[Snakehips]

This would prevent highlighting rows with blanks but, sadly, I think if you extend this approach to nearly 70000 rows it will struggle.

Book1
	F	G
1	LAST NAME (0)	FIRST NAME (0)
2	LEWIS	THOMAS
3	LEWIS	THOMAS
4	JONES	KELLY
5	JONES	KEILY
6	O'BRIEN	CHARLIE
7	JONES	Kelly
8	OBRIEN	CHARLIE
9
10
11	Smithclark	Joe
12	Smith Clark	Joe
13	Scuttle	Fred
14	Smith	Tom
15
Sheet13

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F1:G700	Expression	=SUMPRODUCT((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($G$1:$G$7000&$F$1:$F$7000,"'",""),"-","")," ","") =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($G1&$F1,"'",""),"-","")," ",""))*($G$1:$G$7000>""))>1	text	NO

 

[Newbienew]

This would prevent highlighting rows with blanks but, sadly, I think if you extend this approach to nearly 70000 rows it will struggle.

Book1
	F	G
1	LAST NAME (0)	FIRST NAME (0)
2	LEWIS	THOMAS
3	LEWIS	THOMAS
4	JONES	KELLY
5	JONES	KEILY
6	O'BRIEN	CHARLIE
7	JONES	Kelly
8	OBRIEN	CHARLIE
9
10
11	Smithclark	Joe
12	Smith Clark	Joe
13	Scuttle	Fred
14	Smith	Tom
15
Sheet13

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F1:G700	Expression	=SUMPRODUCT((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($G$1:$G$7000&$F$1:$F$7000,"'",""),"-","")," ","") =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($G1&$F1,"'",""),"-","")," ",""))*($G$1:$G$7000>""))>1	text	NO

I think i see your point. I did get it to work but I do see how it may struggle. The workbook already freezes time to time. I am not sure if it is due to how much data there is, formulas, or the computer memory lol