Excel error checking

[Justin7]

I have a list of 20 names in cells A1 through A20. I want to be sure no names are repeated -- if a name occurs twice in the list, that player is being weighted double which is a problem.

Is there an easy macro to see if any of the names in that range are identical (to give an error message)?

[MonkeyF0cker]

Are you sure the names will be identical?

[Justin7]

Are you sure the names will be identical?

Yes.

[MonkeyF0cker]

    Dim names(0 To 19) As String
    For i = 0 To 19
        names(i) = Range("A1").Offset(i, 0)
    Next
    For i = 0 To 19
        For j = 0 To 19
            If i <> j And names(i) = Range("A1").Offset(j, 0) Then
                 MsgBox ("Duplicate entry at A" & i + 1 & " and A" & j + 1)
            End If
        Next
    Next

[Justin7]

Thanks. The code seems straight forward enough... But I've actually never done macro work in Excel.

Basic question: how do I turn that code into a macro (or even a formula that checks whenever the sheet updates)?

[MonkeyF0cker]

That code repeats the same match.

I edited it to stop the repeat but it would have required some other sort of check in addition to what I added.

So it's back to the original.

[MonkeyF0cker]

Thanks. The code seems straight forward enough... But I've actually never done macro work in Excel.

Basic question: how do I turn that code into a macro (or even a formula that checks whenever the sheet updates)?

http://www.anthony-vba.kefra.com/vba/vbabasic1.htm

[MonkeyF0cker]

Not sure how you want to implement it. You could add it to a button click or just run a macro.

That link shows you how to setup a macro.

[Justin7]

Thanks again.

[thom321]

If you don't have a lot of data you are comparing, you can create a function that works like a regular function in Excel (but slower) that checks a specified range for duplicates.

I adjusted Monkeys code so the function returns the cell address(s) of any duplicate values.

I have attached a workbook that has the function and some test data to show what it would return. You still need to know how to add macros in a workbook to include this in another workbook so I would still follow Monkeys suggestions.

[edek]

quick and dirty

put this in B1:B20

=IF(COUNTIF($A$1:$A$20,A1)>1,"Duplicate" ,"")

[a4u2fear]

put this in B1:B20

=IF(COUNTIF($A$1:$A$20,A1)>1,"Duplicate" ,"")

1 post and 1140 pts?

[gino342]

perhaps i'm missing something but couldn't you simply use the remove duplicates feature under DATA? excel 2010.

[LT Profits]

Seems to me this does not need to be as complicated as some are making it. Just put names alphabetical order in column A and insert column with basic IF statement in column B (i.e., IF(A2=A1,1,0)). As long as all returns are 0 you are fine.

[TomG]

There are lots of ways to do this. A macro should not be the first option, IMO.

[MonkeyF0cker]

I'd much rather use a macro or some VBA routine than writing functions all over the sheet.

You have one place to look (and one thing to modify) if you need to change something.

To each his own I suppose. They all accomplish the same thing in the end.