How to get Exact Matches in a Column

[Suman Mehta]

I need a column like this "Exact Match" column where only the duplicate values where the match is exact (Case sensitive) shows True. Please help.

Name	Exact Match
Coca Cola Bottlers Japan Inc	FALSE
Coca Cola East Japan Co	TRUE
Coca-Cola Bottlers Japan Inc	FALSE
Coca Cola East Japan co	FALSE
Coca Cola East Japan Co	TRUE
San Ai Oil	FALSE
SAN-AI OIL CO	FALSE
San-Ai Oil	FALSE
KI Star Real Estate Co	TRUE
KI Star Real Estate Co	TRUE
KI Star Real Estate	FALSE
KI Star Real estate	FALSE

 

[jasonb75]

There may be other ways, but this one works.

Book2
	A	B
1	Name	Exact Match
2	Coca Cola Bottlers Japan Inc	FALSE
3	Coca Cola East Japan Co	TRUE
4	Coca-Cola Bottlers Japan Inc	FALSE
5	Coca Cola East Japan co	FALSE
6	Coca Cola East Japan Co	TRUE
7	San Ai Oil	FALSE
8	SAN-AI OIL CO	FALSE
9	San-Ai Oil	FALSE
10	KI Star Real Estate Co	TRUE
11	KI Star Real Estate Co	TRUE
12	KI Star Real Estate	FALSE
13	KI Star Real estate	FALSE
Sheet25

Cell Formulas
Range	Formula
B2:B13	B2	=SUMPRODUCT(--EXACT($A$2:$A$13,A2))>1

 

[mehidy1437]

Already answered the same.

 

[VBE313]

Just for sh*ts and giggles, you can use a UDF like this

Function sumanMehta(rng As Range)
Dim lastRow As Long, cl As Object, strTrue As String
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For Each cl In Range("A1:A" & lastRow)
    If StrComp(rng.Value, cl.Value) = 0 And rng.Address <> cl.Address Then
        strTrue = "TRUE"
        Exit For
    Else
        strTrue = "FALSE"
    End If
Next cl
sumanMehta = strTrue
End Function

 

[Suman Mehta]

There may be other ways, but this one works.

Book2
	A	B
1	Name	Exact Match
2	Coca Cola Bottlers Japan Inc	FALSE
3	Coca Cola East Japan Co	TRUE
4	Coca-Cola Bottlers Japan Inc	FALSE
5	Coca Cola East Japan co	FALSE
6	Coca Cola East Japan Co	TRUE
7	San Ai Oil	FALSE
8	SAN-AI OIL CO	FALSE
9	San-Ai Oil	FALSE
10	KI Star Real Estate Co	TRUE
11	KI Star Real Estate Co	TRUE
12	KI Star Real Estate	FALSE
13	KI Star Real estate	FALSE
Sheet25

Cell Formulas
Range	Formula
B2:B13	B2	=SUMPRODUCT(--EXACT($A$2:$A$13,A2))>1

Thank you, Jason. This helped.

 

[Peter_SSs]

Not suggesting this is any better, but another way is

20 09 04.xlsm
	A	B
1	Name	Exact Match
2	Coca Cola Bottlers Japan Inc	FALSE
3	Coca Cola East Japan Co	TRUE
4	Coca-Cola Bottlers Japan Inc	FALSE
5	Coca Cola East Japan co	FALSE
6	Coca Cola East Japan Co	TRUE
7	San Ai Oil	FALSE
8	SAN-AI OIL CO	FALSE
9	San-Ai Oil	FALSE
10	KI Star Real Estate Co	TRUE
11	KI Star Real Estate Co	TRUE
12	KI Star Real Estate	FALSE
13	KI Star Real estate	FALSE
Exact Matches

Cell Formulas
Range	Formula
B2:B13	B2	=SUMPRODUCT(--(SUBSTITUTE(A$2:A$13,A2,"")=""))>1