check for double values
-
- NewLounger
- Posts: 11
- Joined: 07 Feb 2021, 10:35
check for double values
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
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
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: check for double values
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
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: check for double values
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
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
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
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
You can find me at DocAElstein also
-
- NewLounger
- Posts: 11
- Joined: 07 Feb 2021, 10:35
Re: check for double values
Thank you Doc.AElstein. That worked
-
- NewLounger
- Posts: 11
- Joined: 07 Feb 2021, 10:35
Re: check for double values
@HansV @Doc.AElstein
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.
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.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: check for double values
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...
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
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: check for double values
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.
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
You can find me at DocAElstein also
-
- NewLounger
- Posts: 11
- Joined: 07 Feb 2021, 10:35
Re: check for double values
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
Attached is the excel workbook
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: check for double values
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
Hans
-
- NewLounger
- Posts: 11
- Joined: 07 Feb 2021, 10:35
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: check for double values
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 11
- Joined: 07 Feb 2021, 10:35
Re: check for double values
Thank you Hans, didn't found the conditional formatting across sheets myself.
-
- NewLounger
- Posts: 11
- Joined: 07 Feb 2021, 10:35
Re: check for double values
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!
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.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: check for double values
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.
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
Hans
-
- NewLounger
- Posts: 11
- Joined: 07 Feb 2021, 10:35
Re: check for double values
ok thank you
So it are the same 2 rules for all sheets?
So it are the same 2 rules for all sheets?
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: check for double values
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.
=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
Hans