Output first Match Found if Cells contain Duplicate Text

JohnTravolski

New Member
Joined
Nov 25, 2015
Messages
25
Suppose I have several a range of cells, each cell of which contain a string, and I want a function to look at this range and output "FALSE" if all strings in the range are unique from each other and I want it to output the first duplicate it finds if there are at least two cells with the same value. Examples of what I'm wanting:

Cells: A, B, C, D, E
Output: FALSE

Cells: A, B, C, D, A:
Output: A

Cells: A, B, C, B, A:
Output: A

Cells: A, B, C, B, D
Output: B

Is it possible to write an Excel function that does this? (It's not imperative how the 'first' match is defined; what's important is that it just outputs one of the matches.)
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,380
Office Version
2013
Platform
Windows
Try this:
Code:
[FONT=Lucida Console][COLOR=Royalblue]Function[/COLOR] try77(r [COLOR=Royalblue]As[/COLOR] Range) [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] c [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] z
z = False
    
    [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] c In r
        [COLOR=Royalblue]If[/COLOR] WorksheetFunction.CountIf(r, c.Value) > [COLOR=Brown]1[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        z = c.Value: [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]Next[/COLOR]

try77 = z

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Function[/COLOR][/FONT]
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,312
Office Version
365
Platform
Windows
For a worksheet formula approach, try this array formula (requires Excel 2013 or later). It should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

<b>Dupes</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:28px;" /><col style="width:28px;" /><col style="width:28px;" /><col style="width:28px;" /><col style="width:28px;" /><col style="width:30px;" /><col style="width:61px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">A</td><td style="font-size:10pt; ">B</td><td style="font-size:10pt; ">C</td><td style="font-size:10pt; ">D</td><td style="font-size:10pt; ">E</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">FALSE</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">A</td><td style="font-size:10pt; ">B</td><td style="font-size:10pt; ">C</td><td style="font-size:10pt; ">D</td><td style="font-size:10pt; ">A</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">A</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">A</td><td style="font-size:10pt; ">B</td><td style="font-size:10pt; ">C</td><td style="font-size:10pt; ">B</td><td style="font-size:10pt; ">A</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">A</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">A</td><td style="font-size:10pt; ">B</td><td style="font-size:10pt; ">C</td><td style="font-size:10pt; ">B</td><td style="font-size:10pt; ">D</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">B</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G1</td><td >{=IFNA(INDEX<span style=' color:008000; '>(A1:E1,MATCH<span style=' color:#0000ff; '>(TRUE,COUNTIF<span style=' color:#ff0000; '>(A1:E1,A1:E1)</span>>1,0)</span>)</span>,FALSE)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table>


Edit:
(requires Excel 2013 or later)
Otherwise
{=IFERROR(INDEX(A1:E1,MATCH(TRUE,COUNTIF(A1:E1,A1:E1)>1,0)),FALSE)}
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,312
Office Version
365
Platform
Windows
Brilliant! Thank you very much.
You're welcome. :)

(And if you would prefer to avoid the C+S+E confirmation, you can use the marginally longer ...)

<b>Dupes</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:28px;" /><col style="width:28px;" /><col style="width:28px;" /><col style="width:28px;" /><col style="width:28px;" /><col style="width:30px;" /><col style="width:75px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">A</td><td style="font-size:10pt; ">B</td><td style="font-size:10pt; ">C</td><td style="font-size:10pt; ">D</td><td style="font-size:10pt; ">E</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">FALSE</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">A</td><td style="font-size:10pt; ">B</td><td style="font-size:10pt; ">C</td><td style="font-size:10pt; ">D</td><td style="font-size:10pt; ">A</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">A</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">A</td><td style="font-size:10pt; ">B</td><td style="font-size:10pt; ">C</td><td style="font-size:10pt; ">B</td><td style="font-size:10pt; ">A</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">A</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">A</td><td style="font-size:10pt; ">B</td><td style="font-size:10pt; ">C</td><td style="font-size:10pt; ">B</td><td style="font-size:10pt; ">D</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">B</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G1</td><td >=IFNA(INDEX<span style=' color:008000; '>(A1:E1,MATCH<span style=' color:#0000ff; '>(TRUE,INDEX<span style=' color:#ff0000; '>(COUNTIF<span style=' color:#804000; '>(A1:E1,A1:E1)</span>>1,0)</span>,0)</span>)</span>,FALSE)</td></tr></table></td></tr></table>
 

JohnTravolski

New Member
Joined
Nov 25, 2015
Messages
25
For some reason your code is now a jumble of html. I put it in an html document to view it and will post a screenshot here so people can see what you wrote:

 

Forum statistics

Threads
1,082,500
Messages
5,365,935
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top