How do I find the cell address of duplicates using VBA

Question

I'm brand new to using VBA within excel. I'm not even 100% sure on how to insert a module correctly to begin with so this will be a big help.

I've set up a worksheet that randomizes a number between 1 and 100.

B3  =RANDBETWEEN(C6,F6)

I have 13 contestants. Each gets to guess a number. Goal is to be closest to the randomized number. (Guess a number between x & y. Closest wins "The Prize")

Contestants are listed in A9:B21. (i.e; "Contestant #1")

Their guesses are listed in C9:C21.

The difference between the randomized number and the guess is listed in D9:D21

D9:D21  =IF(C9>$B$3,C9-$B$3,IF($B$3>C9,$B$3-C9,0))

Cells F9:F21 let you know who won and doesn't count any guesses that are less than 1 and more than 100.

F9:F21  =IF(C9<1,,IF(C9>100,,IF(D9=MIN($D$9:$D$21),A9&" Wins",)))

Unfortunately, every time I try to reference in cell C6 or F6 instead of 1 or 100 I only get the result of 0.

In F8 I have a notification that pops up if there is a tie. Still not sure if this code is the best way to do this.

F8  =IF(COUNTIF(F9:F21,"*")>1,"Tie Breaker Needed","")

Here's my question. I know how to recognize duplicates and I can highlight them if I want to. I can't seem to find a way to have a single cell tell me exactly who has won even if there is a tie.

I.e; If Contestant #7 Wins --- Cell would say "Contestant #7 Wins" If Contestants #7 & #10 win --- Cell should say Contestant #7 & Contestant #10 Tie.

Is there a command or VBA module that could do this for me? I tried the VBA module below that I found but it only returns #NAME? No matter what I do.

Either this code works and I'm not inserting the module correctly or this module doesn't work for my situation and I need something new.

Help me Oh Great Excel Sages of the Online Realm.

Image of My Excel Worksheet

Option Explicit

Function LookupCSVResults(lookupValue As Integer, lookupRange As Range, resultsRange As Range) As String

    Dim s As String 'Results placeholder
    Dim sTmp As String  'Cell value placeholder
    Dim r As Long   'Row
    Dim c As Long   'Column
    Const strDelimiter = "|||"  'Makes InStr more robust

    s = strDelimiter
    For r = 1 To lookupRange.Rows.Count
        For c = 1 To lookupRange.Columns.Count
            If lookupRange.Cells(r, c).Value = lookupValue Then
                'I know it's weird to use offset but it works even if the two ranges
                'are of different sizes and it's the same way that SUMIF works
                sTmp = resultsRange.Offset(r - 1, c - 1).Cells(1, 1).Value
                If InStr(1, s, strDelimiter & sTmp & strDelimiter) = 0 Then
                    s = s & sTmp & strDelimiter
                End If
            End If
        Next
    Next

    'Now make it look like CSV
    s = Replace(s, strDelimiter, ",")
    If Left(s, 1) = "," Then s = Mid(s, 2)
    If Right(s, 1) = "," Then s = Left(s, Len(s) - 1)

    LookupCSVResults = s 'Return the function

End Function

It doesn't have to be done in VBA. I just hadn't found a way to do it in a single cell without using VBA until now. — Jordan Moffatt
My contestants are in A1:C11 with B & C being their predictions. The actual results are in E2 & F2 — Nathan_Sav
H2 to H11, you have the array formula =IFERROR(SMALL(IF((($B$2:$B$11=$E$2)*($C$2:$C$11=$F$2)),ROW($B$2:$B$11)-1),ROWS($B$2:$B3)),"") — Nathan_Sav
I2 to I11, you have the normal formula =IFERROR(INDEX($A$2:$A$11,H2,1),"") — Nathan_Sav

Gary's Student Gary's Student · Accepted Answer · 2016-01-06T16:15:23

How about the following UDF():

Option Explicit

Public Function ListWinners(people As Range, deltas As Range) As String
   Dim wf As WorksheetFunction
   Dim i As Long, delta As Long, msg As String
   Dim WinningValue As Long
   Set wf = Application.WorksheetFunction
   ListWinners = ""
   msg = " wins"
   WinningValue = wf.Min(deltas)
   For i = 1 To deltas.Rows.Count
      If deltas(i) = WinningValue Then
         If ListWinners = "" Then
            ListWinners = people(i)
         Else
            ListWinners = ListWinners & " & " & people(i)
            msg = " tie"
         End If
      End If
   Next i
   ListWinners = ListWinners & msg
End Function

In your posted example, use it like:

=ListWinners(A9:A21,C9:C21)

to display the winner list in a single cell.

EDIT#1:

User Defined Functions (UDFs) are very easy to install and use:

ALT-F11 brings up the VBE window
ALT-I ALT-M opens a fresh module
paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

bring up the VBE window as above
clear the code out
close the VBE window

To use the UDF from Excel:

=ListWinners(A1:A100,B1:B100)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

How do I find the cell address of duplicates using VBA

1 Answers