Search code examples
excelvbaduplicatesmessagebox

Finding duplicates in different sheets or giving a message box if duplicate is found

I have tried several options already but can't get what I want. I have a workbook of 31 sheets and each sheet has a column where number entries are entered. There can be duplicates in one sheet but there shouldn't be duplicates among different sheets. How should I do it?

So far I have come up with another sheet which takes all the values from other 31 using IF and with the help of a macro button removes duplicates in each column (for each sheet). There is a conditional formatting on top so it shows in that sheet whether duplicate was entered or not. However, it would be best if there would be a popup saying there is a duplicate.

In the sheet with all the entries I also have 0 and Empty values which will be in every column in every case.

Best solution would be to have a button which can be pressed whenever the check is needed.

Thanks for any suggestions!

Did i forget to mention that I am a total noob in terms of VBA? I do not think things that I tried are relevant. As I only need the code for the button to be placed in each sheet "Check" which would check the sheet with all the values and give a message if a duplicate is found, except for 0 and Empty.

Solution

  • You don't need VBA. Even though I am a software developer I try to use as little VBA as I can.

    Lets say your values of all 31 sheets are on every sheet in A1-A10. Then do the following per sheet:

    First create a column with the unique values of the sheet. For that you need a "matrix formula". Skip C1 and type the following formula in cell C2 and close with CTRL-SHIFT-ENTER (not just ENTER!):

    =IFERROR(INDEX($A$1:$A$10, MATCH(0, IF(ISBLANK($A$1:$A$10),1,COUNTIF($C$1:C1, $A$1:$A$10)), 0)),"")

    Now you get a matrix formula. As you can see there are now brackets {} around the formula. Now fill the formula down to C11 and you get only the unique values of A1-A10. After you did this 31 times, reference all 31 columns C to an overview sheet.

    You can see the duplicates in the overview sheet with conditional formatting. It is explained here. With this solution it will be very easy to pinpoint the duplicates.

    But if you want to have values instead of formatting, create a second overview sheet and put this formula in A1:

    =IF(ISNUMBER(OverviewSheet!A1),IF(OverviewSheet!A1<>0,COUNTIF(OverviewSheet!$A$1:$AE$10,OverviewSheet!A1)>1))

    and fill this formula to cell AE10. You will get a whole range of TRUE/FALSE. This formula also takes empty cell and zeros into account.

    Now place this formula somewhere on the second overview sheet:

    =COUNTIF(A1:AE10,TRUE())>0

    Now you have one cell that contains only TRUE or FALSE if you have duplicates over your 31 sheets.

    If you want this result under a button I am convinced you will be able to create a macro for this.

    Maybe there are shorter results, but this is what I came up with.

    Success!

    EDIT:

    OK, maybe I was too positive about VBA. Let's say the last formula is in AF1, then this is your macro:

    Sub Check()
    If ThisWorkbook.Sheets("OverviewSheet2").Range("AF1").Value = True Then
        MsgBox "We have duplicates!"
    Else
        MsgBox "We have no duplicates."
    End If
End Sub

    Now the most simple option for this macro is to put in under a shortcut-key (View -> Macros -> View Macros -> Select 'Check' macro -> Options... -> Shortcut key), e.g. CTRL-SHIFT-D. Then you don't need 31 buttons on every sheet.