How to get Exact Matches in a Column

Suman Mehta

New Member
Joined
Aug 31, 2020
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
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.


NameExact Match
Coca Cola Bottlers Japan IncFALSE
Coca Cola East Japan CoTRUE
Coca-Cola Bottlers Japan IncFALSE
Coca Cola East Japan coFALSE
Coca Cola East Japan CoTRUE
San Ai OilFALSE
SAN-AI OIL COFALSE
San-Ai OilFALSE
KI Star Real Estate CoTRUE
KI Star Real Estate CoTRUE
KI Star Real EstateFALSE
KI Star Real estateFALSE
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
There may be other ways, but this one works.
Book2
AB
1NameExact Match
2Coca Cola Bottlers Japan IncFALSE
3Coca Cola East Japan CoTRUE
4Coca-Cola Bottlers Japan IncFALSE
5Coca Cola East Japan coFALSE
6Coca Cola East Japan CoTRUE
7San Ai OilFALSE
8SAN-AI OIL COFALSE
9San-Ai OilFALSE
10KI Star Real Estate CoTRUE
11KI Star Real Estate CoTRUE
12KI Star Real EstateFALSE
13KI Star Real estateFALSE
Sheet25
Cell Formulas
RangeFormula
B2:B13B2=SUMPRODUCT(--EXACT($A$2:$A$13,A2))>1
 
Upvote 0
Just for sh*ts and giggles, you can use a UDF like this

VBA Code:
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
 
Upvote 0
There may be other ways, but this one works.
Book2
AB
1NameExact Match
2Coca Cola Bottlers Japan IncFALSE
3Coca Cola East Japan CoTRUE
4Coca-Cola Bottlers Japan IncFALSE
5Coca Cola East Japan coFALSE
6Coca Cola East Japan CoTRUE
7San Ai OilFALSE
8SAN-AI OIL COFALSE
9San-Ai OilFALSE
10KI Star Real Estate CoTRUE
11KI Star Real Estate CoTRUE
12KI Star Real EstateFALSE
13KI Star Real estateFALSE
Sheet25
Cell Formulas
RangeFormula
B2:B13B2=SUMPRODUCT(--EXACT($A$2:$A$13,A2))>1
Thank you, Jason. This helped.
 
Upvote 0
Not suggesting this is any better, but another way is

20 09 04.xlsm
AB
1NameExact Match
2Coca Cola Bottlers Japan IncFALSE
3Coca Cola East Japan CoTRUE
4Coca-Cola Bottlers Japan IncFALSE
5Coca Cola East Japan coFALSE
6Coca Cola East Japan CoTRUE
7San Ai OilFALSE
8SAN-AI OIL COFALSE
9San-Ai OilFALSE
10KI Star Real Estate CoTRUE
11KI Star Real Estate CoTRUE
12KI Star Real EstateFALSE
13KI Star Real estateFALSE
Exact Matches
Cell Formulas
RangeFormula
B2:B13B2=SUMPRODUCT(--(SUBSTITUTE(A$2:A$13,A2,"")=""))>1
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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
Back
Top