check for double values

jdg072
NewLounger
Posts: 11
Joined: 07 Feb 2021, 10:35

check for double values

Post by jdg072 »

I want to check my entire workbook (all excel sheets) for identical values. How can I do that?
ex: If I enter a value "123456" somewhere, I want to check visually if that value is already used in the entire workbook.

Many Thanks, Johan

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: check for double values

Post by HansV »

Do you want to do this for a specific value, or do you want to find all duplicates in all rows and columns of all worksheets in the entire workbook?
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: check for double values

Post by Doc.AElstein »

Hi Johan
I had started doing a solution idea before Hans posted, and I had assumed the simplest case of you wanting to check if a value you type in is anywhere in your workbook worksheets
So I will post anyway…

I think doing this sort of thing can be done in VBA in a few ways.
Simplest is perhaps to loop for all worksheets, then try to find in each UsedRange the number that you just typed in
We could put coding into an “event macro”. – This will automatically run when you change a cell value

This macro has to go in your ThisWorkbook code module

Code: Select all

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Value = "" Then
    ' you probably don't want to do anything for an empty cell entry
    Else
    Dim UrVl As Variant: Let UrVl = Target.Value  '  store the thing you typed in so we can search for it later
     Let Application.EnableEvents = False         '  we have to disable coding like this one or it will set itself off in the neext code line
     Target.ClearContents ' get rid of the thing you typed in temporarily, so i don't find it
     Let Application.EnableEvents = True
    Dim Ws As Worksheet
        For Each Ws In Worksheets
        Dim FandNuver As Range
         Set FandNuver = Ws.UsedRange.Find(what:=UrVl, after:=Ws.UsedRange.Item(1), LookIn:=xlValues, Lookat:=xlWhole, Searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=True)
            If FandNuver Is Nothing Then
            ' case i did not find what was typed in - do nothing
            Else
             MsgBox prompt:="Got that already in " & FandNuver.Parent.Name & " at " & FandNuver.Address
            End If
        Next Ws
     Let Application.EnableEvents = False
     Let Target.Value = UrVl                      ' put the thing back in that you typed
     Let Application.EnableEvents = True
     End If
End Sub
I put that macro in the uploaded workbook
If you type anything anywhere in the uploaded workbook, then if that is already in a worksheet cell anywhere in the workbook , then that macro should tell you about it

Alan
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

jdg072
NewLounger
Posts: 11
Joined: 07 Feb 2021, 10:35

Re: check for double values

Post by jdg072 »

Thank you Doc.AElstein. That worked

jdg072
NewLounger
Posts: 11
Joined: 07 Feb 2021, 10:35

Re: check for double values

Post by jdg072 »

@HansV @Doc.AElstein
HansV wrote:
07 Feb 2021, 17:17
Do you want to do this for a specific value, or do you want to find all duplicates in all rows and columns of all worksheets in the entire workbook?
Doc.Aelstein solution works, but gives a lot of debug error when working with it, so it would be better if just the 2 cells with the duplicate values colors RED and clears up when the value is deleted or changed. Conditional formatting works on sheet, but not across multiple sheets.

It is all values in all sheet, colums and rows. I just needed to make sure I use no 2 same number in my entire excel workbook.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: check for double values

Post by HansV »

Conditional formatting can work across worksheets, but it would make entering data very slow.
An on-demand check (for example by clicking a command button) is more efficient.

Does your requirement really apply to ALL cells in ALL worksheets? That seems strange to me...
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: check for double values

Post by Doc.AElstein »

I expect if I modified the macro, or did another ,to change the duplicate cells to red, then get them to change back again, then the macro would be a bit more complicated and so likely have some more problems.

A better approach might be to give us more details of what errors you are having and when etc. If its possible to share a workbook with us then that would be better still.
Its difficult to make a full proof macro without knowing more detail of what you are doing.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

jdg072
NewLounger
Posts: 11
Joined: 07 Feb 2021, 10:35

Re: check for double values

Post by jdg072 »

I just need to know if I enter a new "library part number", if it is free or if it is already used.
Attached is the excel workbook
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: check for double values

Post by HansV »

Nummer is in columns B and I on the SS sheet, and in columns B and J on the other sheets. This inconsistency complicates it. Would it be possible to use the same columns on all sheets?
Best wishes,
Hans

jdg072
NewLounger
Posts: 11
Joined: 07 Feb 2021, 10:35

Re: check for double values

Post by jdg072 »

HansV wrote:
08 Feb 2021, 13:15
Nummer is in columns B and I on the SS sheet, and in columns B and J on the other sheets. This inconsistency complicates it. Would it be possible to use the same columns on all sheets?
Yes , I can use B & I column everywere, if that makes it easier.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: check for double values

Post by HansV »

Here is a version that uses conditional formatting on columns B and I. It checks across all worksheets. Since there is no code, it is now a .xlsx workbook.

library.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

jdg072
NewLounger
Posts: 11
Joined: 07 Feb 2021, 10:35

Re: check for double values

Post by jdg072 »

Thank you Hans, didn't found the conditional formatting across sheets myself.

jdg072
NewLounger
Posts: 11
Joined: 07 Feb 2021, 10:35

Re: check for double values

Post by jdg072 »

should I add that same formula in every sheet?
I narrowed my example to 4 sheets, but in reality It has 15 sheets, that becomes along formula, but thanks, it works!
Last edited by jdg072 on 08 Feb 2021, 14:42, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: check for double values

Post by HansV »

There are two rules for every sheet: one for column B and one for column I.
If you have more sheets than the ones in your sample workbook, you have to edit the formulas for the conditional formatting rules to take all sheets ibto account, and create those two rules for all sheets.
Best wishes,
Hans

jdg072
NewLounger
Posts: 11
Joined: 07 Feb 2021, 10:35

Re: check for double values

Post by jdg072 »

ok thank you
So it are the same 2 rules for all sheets?

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: check for double values

Post by HansV »

You have to edit each of the formulas, because they depend on the sheet. For example, the rule for column B on the SS sheet uses the formula

=IF(ISNUMBER(B1),(COUNTIF($B:$B,B1)+COUNTIF($I:$I,B1)+COUNTIF(HDPE!$B:$B,B1)+COUNTIF(HDPE!$I:$I,B1)+COUNTIF(PVC!$B:$B,B1)+COUNTIF(PVC!$I:$I,B1)+COUNTIF(PP!$B:$B,B1)+COUNTIF(PP!$I:$I,B1))>1)

In the first two COUNTIFS, the sheet name is not mentioned because they refer to SS itself. But the rule for column B on the HDPE sheet is

=IF(ISNUMBER(B1),(COUNTIF(SS!$B:$B,B1)+COUNTIF(SS!$I:$I,B1)+COUNTIF($B:$B,B1)+COUNTIF($I:$I,B1)+COUNTIF(PVC!$B:$B,B1)+COUNTIF(PVC!$I:$I,B1)+COUNTIF(PP!$B:$B,B1)+COUNTIF(PP!$I:$I,B1))>1)

This time, the 3rd and 4th COUNTIF don't mention the sheet name because they refer to HDPE itself. Etc.

I created the formula as

=IF(ISNUMBER(B1),(COUNTIF(SS!$B:$B,B1)+COUNTIF(SS!$I:$I,B1)+COUNTIF(HDPE!$B:$B,B1)+COUNTIF(HDPE!$I:$I,B1)+COUNTIF(PVC!$B:$B,B1)+COUNTIF(PVC!$I:$I,B1)+COUNTIF(PP!$B:$B,B1)+COUNTIF(PP!$I:$I,B1))>1)

in Notepad, then copied/pasted it into the rules for column B on each of the sheets. When you save the rule, Excel removes the sheet name of the active sheet.

It's similar for the rule for column I, only with I1 instead of B1.
Best wishes,
Hans

jdg072
NewLounger
Posts: 11
Joined: 07 Feb 2021, 10:35

Re: check for double values

Post by jdg072 »

ok Thank you