To find the duplicate values across the worksheet

May 2, 2015 at 14:03:29
Specs: Windows 7
Hi, I was wondering if anyone could help with conditional formatting across 25 worksheets to capture duplicate values across all worksheets (not within the one worksheet they all need to be linked). I have to insert the serial number of the product in two columns(some valuable product need only one cell to insert the serial number, so i merged the two cells of the two column to get a single cell) and there can never be a duplicate within any of them, across all 25 sheets. I need it for the whole two nearest column(I have merged some of the cells in these columns), I have tried formatting one whole column (which works within that column) and then used paint to format to another worksheet but it did not work? Stuck any advice would be much appreciated.

See More: To find the duplicate values across the worksheet

Report •


#1
May 2, 2015 at 14:55:32
conditional formatting across 25 worksheets
To use CF across worksheets the easiest way is to use a Named Range.

I have to insert the serial number of the product in two columns(some valuable product need only one cell to insert the serial number, so i merged the two cells of the two column to get a single cell) and there can never be a duplicate

Using Merged cells can be a cause of problems.

I actually think what you need is possibly a VLOOKUP()

You enter the serial number,
do a VLOOKUP(),
it reports back it the number already exists or not.

We would need more specific info,
IE Column Letters, Row Numbers, etc.
to offer more help.

And of course, there is always using a MACRO to get the data.

MIKE

http://www.skeptic.com/


Report •

#2
May 2, 2015 at 20:00:31
re: "I have to insert the serial number of the product in two columns"

I'm a little confused by this. Do you mean you have to enter the serial number twice or do you mean that a single serial number is split across 2 cells?

As Mike said, we need some more detail before we could offer any help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
May 3, 2015 at 04:11:58
Hi Mike and Derby

I didn't got your point, if you want i can send the excel work book for your reference.
hope you can give some solution for that.


Report •

Related Solutions

#4
May 3, 2015 at 05:50:15
Our point was that you didn't provide enough detail for us to answer your question. We don't know what your data looks like, where it is located (Sheet, Row, Column), where we would be searching for duplicates, what process you use to enter the data, when would you want the search to take place, etc.

You can either provide more detail in this forum, including same sample data, or you can upload the file to a site like zippyshare, DropBox, etc. and then post the link in your next response.

If you are going to post example data in this forum, please click on the following line and read the instructions on the correct way to do that.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
May 3, 2015 at 11:21:44
Hi DerbyDad03,
As you said I shared the workbook named New Inventory in Zippy share and I attach the link bellow,

http://www43.zippyshare.com/v/0WdgU...

In that work book, i need the conditional formating to find the duplicate values for the columns F and G if we enter the same Sr No/ Tag number in any othe work sheets in the work book( Note only the same column F and G in all the sheets in the work book).
Also the CF is aplicable to only the raws( 15-32 and 64-70).Hope you can solve this and send the modified file. My mail ID (email address deleted by moderator.)

edited by moderator: Deleted email address


Report •

#6
May 3, 2015 at 17:18:46
I have deleted your email address. You should never post your email address in plain text in a public forum.

Even if you don't care if you get a lot of spam, we don't want to be known as a forum where email addresses can be harvested. Once the bad guys start hanging around, it is impossible to get rid of them.

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
May 4, 2015 at 02:49:39
I apologize for the above..

Is that possible to get the solution for my problem..??

message edited by amrugcc


Report •

#8
May 4, 2015 at 04:40:16
Give us time. We're all volunteers here and will work on a solution as time allows. If it can't be done with CF, will you accept a Macro solution?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
May 4, 2015 at 05:01:14
Hi DerbyDad03,

Thanks for the time..thats okay for me. I need the solution.It would be great if you found CF.


Report •

#10
May 4, 2015 at 12:35:20
I was unable to find a CF method to accomplish your goal. I'm not saying that a CF solution can't be found, just that I wasn't able to find anything that worked.

However, I believe that this macro will do what you want. Since I assume that serial number could be entered into any of the worksheets, this code must be placed in the ThisWorkbook module so that it will fire when a change is made to the specified range (F15:G32,F64:G70) in any sheet.

As written, if a duplicate value is found, the code will present a message box that tells you where the duplicates are located. Please note that once the code finds a single duplicate of the most recent entry, it will present the message box. If there are 3 or more matching entries, the code will only tell you about the first 2 since it stops searching as soon as a duplicate is found. Let me know if that is a problem.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Determine if change was made to a single cell
  If Target.Cells.Count = 1 Then
'Determine if the change was made within specified range
   If Not Intersect(Target, Range("F15:G32,F64:G70")) Is Nothing Then
'Ensure that change was not a deletion of value
     If Target <> "" Then
'If a value was entered, then loop through all worksheets
       For Each Sh In Worksheets
'Search for each occurrence of value entered
        With Sh.Range("F15:G32,F64:G70")
        'MsgBox newVal.Address
         Set newVal = .Find(Target, lookat:=xlWhole, LookIn:=xlValues)
          If Not newVal Is Nothing Then
            firstAddress = newVal.Address
           Do
'Build address string for duplicates
            dupFound = dupFound & Sh.Name & " " & newVal.Address & ", "
'Increment counter each time value is found
            newValYes = newValYes + 1
'If the count is greater than 1, then inform user that a
'duplicate exists and where it was found
               If newValYes > 1 Then
                 MsgBox "Duplicate Value Found " & vbCrLf & _
                         Left(dupFound, Len(dupFound) - 2)
                 Exit Sub
               End If
            Set newVal = .FindNext(newVal)
           Loop While Not newVal Is Nothing And newVal.Address <> firstAddress
          End If
        End With
       Next
     End If
   End If
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
July 26, 2015 at 12:50:03
Hello DerbyDad03...

I tried your VB script in my work book but when I enter some serial number in my workshee it shows some error msg and also indicate yellow higlated lines in my vb script. The highlighted script line mentioned bellow...

If Not Intersect(Target, Range("F15:G32,F64:G70")) Is Nothing Then


Report •

#12
July 27, 2015 at 05:05:05
Hello DerbyDad,

Hope you got my point on this regard..

Thanks


Report •

#13
July 27, 2015 at 05:55:48
First, let's make sure we use the correct terminology. The code I posted is not a VB script. It is a Macro written using VBA. VBS (Visual Basic Script) is different than VBA (Visual Basic for Applications). VBA code must be "hosted" by an application, e.g. Excel, Word, etc.

Please refer to this link to read about the differences.

http://musannif.blogspot.com/2008/0...

Second, the suggested code was posted 2 1/2 months ago. Have you not tried it since it was posted or has it been working and is now failing?

I am not having any problem with the code. I get no errors and it presents the message box with the address(es) of the duplicate entries.

Please post a new copy of your workbook at zippyshare, with the code stored in the ThisWorkbook module as explained in Response #10, and I will look at it later. I cannot access zippyshare at work, so nothing will be done until at least this evening, EST.

It would also help if you explained the exact method you use for entering serial numbers in the workbook. The best way for us to duplicate the error is to duplicate your process.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
July 27, 2015 at 11:29:37
Hi DerbyDad03,

I apologize for the incorrect terminology and also for the late response for your valuable time that happened before. Its because of some personal issues happened in my life..anyway...

I will tell you the step by step procedure that i had done,

- Select the first sheet and press Alt+11
- In VBA project double click ThisWorkbook to open macro writing page
- In writing page, First Drop down select workbook and in second drop down Sheet change
- Copy paste the Macro writing that you had send to me except first line and last line(End sub)
- Go to Excel page and write some of the serial no( J32-ff5434S) in one of the cell(Sheet no 1 cell# F17) and press enter
- Suddenly appear a dialog box telling
:- Run-time error '1004':
:- Method 'Intersect' of object'_Global' failed
and give me two tabs. End and Debug.
- I press debug, it goes to macro writing page where the line in macro writing that i mentioned bellow are Highlated with yellow color.

If Not Intersect(Target, Range("F15:G32,F64:G70")) Is Nothing Then

Also I cannot save the file with VBA after doing the above, the excel ask to save the file without VBA. If i do so it will erase all the Macro writing when I open next time.

I would send the Zippyshare link of my excel workbook bellow please take a look.
http://www95.zippyshare.com/v/syX4Z...

Please give me one solution for this..

Thanks for your time..

message edited by amrugcc


Report •

#15
July 27, 2015 at 13:06:26
I will not be able to access zippyshare until later this evening, but in the meantime, here are my responses to your steps:

- Select the first sheet and press Alt+11

I assume you mean Alt+F11

- In VBA project double click ThisWorkbook to open macro writing page

Correct.

- In writing page, First Drop down select workbook and in second drop down Sheet change
- Copy paste the Macro writing that you had send to me except first line and last line(End sub)

There is no real need to perform these steps. You can simply copy the entire macro from my post into the VBA editor. The first and last lines are already included. The VBA editor creates the first and last lines merely as a convenience, but you can just as easily type them in yourself or paste them directly into the editor. In other words, there is nothing magic about what the VBA editor does when it creates those 2 lines other than provide the correct syntax, which is already in the code I posted.

- Go to Excel page and write some of the serial no( J32-ff5434S) in one of the cell(Sheet no 1 cell# F17) and press enter
- Suddenly appear a dialog box telling
:- Run-time error '1004':

When I write a value in F17, nothing happens because that is the only occurrence of the value in the new workbook that I created. However, if I then Copy/Paste that value into any cell within the ranges F15:G32, F64:G70 on any sheet, I get the message box telling me the location of the duplicates. In other words, no errors. The code does exactly what I expect it to do.

Also I cannot save the file with VBA after doing the above, the excel ask to save the file without VBA. If i do so it will erase all the Macro writing when I open next time.

Are you sure that you are reading the Save message from Excel correctly? When you try to save a file that contains a macro in Excel 2007 or later and the file type is set to the default of xlsx, Excel will ask you if you want "To continue saving as a macro-free workbook, click Yes". However, it also gives you the option to click No, so that you can change the file type to xlsm or xlsb. Once you do that, you can save the file with the macro.

If Excel is not giving you those options, then something weird is going on. Perhaps the file you posted at zippyshare will help us figure out what is going on.

Have you tired this in a new workbook or only in the existing workbook?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#16
July 27, 2015 at 13:43:11
Hi DerbyDad03,

Thanks for the solution..It works now..I saved my file as xlms file..and once again thank you very much Mr. Derdydad03..

One more thing I have to ask regarding with this..If it is possible to increase or decrease the raws within our range of a perticular sheet. If I want to do so what i have to do, I mean i need to change the macro or automatically that will change..?

Thanks for your time..


Report •

#17
July 27, 2015 at 16:12:19
I'm glad you were able to get the code working.

The range written into the macro (F15:G32, F64:G70) will not automatically change if you add rows to a spreadsheet. The way to handle that is to use Named Ranges in the workbook and then use the Range Name in the code. When the code references the Named Range it will "ask" Excel what cells make up that range and then use that for the search.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question