Excel error checking

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Justin7
    SBR Hall of Famer
    • 07-31-06
    • 8577

    #1
    Excel error checking
    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
    SBR Posting Legend
    • 06-12-07
    • 12144

    #2
    Are you sure the names will be identical?
    Comment
    • Justin7
      SBR Hall of Famer
      • 07-31-06
      • 8577

      #3
      Originally posted by MonkeyF0cker
      Are you sure the names will be identical?
      Yes.
      Comment
      • MonkeyF0cker
        SBR Posting Legend
        • 06-12-07
        • 12144

        #4
        Code:
            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
        Last edited by MonkeyF0cker; 04-02-12, 07:42 PM. Reason: This repeats notification of same duplicate.. not sure if that's an issue
        Comment
        • Justin7
          SBR Hall of Famer
          • 07-31-06
          • 8577

          #5
          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)?
          Comment
          • MonkeyF0cker
            SBR Posting Legend
            • 06-12-07
            • 12144

            #6
            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.
            Comment
            • MonkeyF0cker
              SBR Posting Legend
              • 06-12-07
              • 12144

              #7
              Originally posted by 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)?
              Comment
              • MonkeyF0cker
                SBR Posting Legend
                • 06-12-07
                • 12144

                #8
                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.
                Comment
                • Justin7
                  SBR Hall of Famer
                  • 07-31-06
                  • 8577

                  #9
                  Thanks again.
                  Comment
                  • thom321
                    SBR High Roller
                    • 06-17-11
                    • 112

                    #10
                    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.
                    Attached Files
                    Comment
                    • edek
                      SBR Rookie
                      • 07-30-11
                      • 1

                      #11
                      quick and dirty

                      put this in B1:B20

                      =IF(COUNTIF($A$1:$A$20,A1)>1,"Duplicate" ,"")
                      Comment
                      • a4u2fear
                        SBR Hall of Famer
                        • 01-29-10
                        • 8147

                        #12
                        Originally posted by edek
                        put this in B1:B20

                        =IF(COUNTIF($A$1:$A$20,A1)>1,"Duplicate" ,"")
                        1 post and 1140 pts?
                        Comment
                        • gino342
                          SBR Rookie
                          • 11-11-10
                          • 41

                          #13
                          perhaps i'm missing something but couldn't you simply use the remove duplicates feature under DATA? excel 2010.
                          Comment
                          • LT Profits
                            SBR Aristocracy
                            • 10-27-06
                            • 90963

                            #14
                            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.
                            Comment
                            • TomG
                              SBR Wise Guy
                              • 10-29-07
                              • 500

                              #15
                              There are lots of ways to do this. A macro should not be the first option, IMO.
                              Comment
                              • MonkeyF0cker
                                SBR Posting Legend
                                • 06-12-07
                                • 12144

                                #16
                                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.
                                Last edited by MonkeyF0cker; 04-03-12, 05:20 PM.
                                Comment
                                SBR Contests
                                Collapse
                                Top-Rated US Sportsbooks
                                Collapse
                                Working...