+ Reply to Thread
Results 1 to 19 of 19

Finding specific cell values within multiple worksheets and checking for duplicates

  1. #1
    Forum Contributor
    Join Date
    04-05-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    360

    Finding specific cell values within multiple worksheets and checking for duplicates

    I'm not sure exactly what the best approach is for this, but I'll do my best to explain what I'm looking for accomplish and I also attached an example with some notes within the workbook too.

    Each worksheet (tab 1-30 & 31-60 in the attached) will have 30 "sets" for specific data entry. For example tab "1-30" the first set are rows 12-31, set 2 is rows 35-54, etc.

    Each "set" will contain 3 data points. A "group", "ID#" & "type"

    A "group" will be a numeric value in the format of 1-1, 1-2, 2-1, 2-2, etc. A "group" number will be entered in multiple "sets"
    An "ID#" is a numeric value related to the group. for example group 1-1 will have ID#'s 1-1-1,1-1-2,1-1-3, etc.
    Each "ID#" will have 2 types. Either "+" or "-". for example 1-1-1 +, 1-1-1 -

    At the end of the data entry each ID# should have both types.

    The goal is that i want to make sure there are no duplicate "ID" values with the same "type" value within the entire workbook. For example there cannot be (2) 1-1-1 +, 1-1-1 +

    I'm thinking I need a macro to scan for duplicates and maybe highlight the "ID#" cells RED if it contains a duplicate. The ideal thing would be once you enter a duplicate ID# & Type the cell would automatically turn RED or a message box could appear.


    So in the attached example I'll hopefully further explain the above.

    in worksheet 1-30

    Set 1 = rows 12-31
    the "group" numbers are in column I
    the "ID#"s are in multiple columns: O, W, AE, AM, AU, BC, etc.
    the "types" are in multiple columns next to the ID#'s: Q, Y, AG, etc.

    Cells O19/Q19 & W21/Y21 both contain the ID/type of 1-1-1+ (which is a problem).

    I hope this isn't too confusing, I tried my best to simplify the basics of the data entry. Any help is much appreciated, even just a suggestion on the best approach to accomplish this would be helpful.

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    Do you want to find the duplicates in each set? For example, in Sheet 1-30 you have duplicates in Set 1 which are highlighted in red (1-1-1 +). If there was one occurrence of 1-1-1 + in Set 2, did you want that highlighted as well? What about duplicates across the 2 worksheets?
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Contributor
    Join Date
    04-05-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    360

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    It would find any duplicate throughout all worksheets. There should only be 1 occurrence of (1-1-1+).
    Last edited by adamheon; 04-02-2018 at 11:43 AM.

  4. #4
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    And if exists some duplicate what to do ???
    - Battle without fear gives no glory - Just try

  5. #5
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    See next code
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    04-05-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    360

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    Hi PCI, thanks for the code! I'd like to have the message box to pop up once the duplicate is entered (and if possible, when you click OK i'd like it to bring you to the cell where the first entry is made).

    Or with the current code you posted, if it could highlight the duplicate entries that would work too. Something to easily identify the duplicate entries.

    Thanks for your help!

  7. #7
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    "i'd like it to bring you to the cell where the first entry is made" how to manage when exist several duplicate ?

  8. #8
    Forum Contributor
    Join Date
    04-05-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    360

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    Yeah that option would only work if it could identify the duplicate the moment it was entered. that way you couldn't have more than 1 duplicate at a time.

    Otherwise i think the only way is to highlight the duplicate entries.

  9. #9
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    OK see next macro and launch it until all duplicates are found are corrected of course
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    04-05-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    360

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    That works great!

    Would there be a way in which you can toggle through the duplicates? Since it brings you to the first duplicate, potentially that could be the correct value and the error is in another entry. So if i could toggle between the duplicates and see where exactly they are i could then figure out which entry is the incorrect one.

  11. #11
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    See a proposal and watch sheet "Duplicate"
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by PCI; 04-03-2018 at 01:33 PM.

  12. #12
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    In addition to previous macro you could add:
    in a module

    Please Login or Register  to view this content.
    in sheets "Duplicate" in tab code
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    04-05-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    360

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    This is great! Thank you.

    I got everything working on my sheet expect for that last feature you noted to jump to the cell, which is awesome!

    I'm getting a runtime error 9, subscript out of range

    it brings me to the module code:

    Please Login or Register  to view this content.
    the only thing i changed was where this duplicate list is on the sheet (row 58 instead of 2), here is the change:

    Please Login or Register  to view this content.

  14. #14
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    When the bug happen if you shift the cursor just over "WsName" it should display the value of "WsName" is it the right value ???

  15. #15
    Forum Contributor
    Join Date
    04-05-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    360

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    it says WsName = 6

    Not sure where thats coming from?

  16. #16
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    Can you show a data example in sheet "Duplicate" in the columns concerned 58 to 61
    Of course you change next code
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  17. #17
    Forum Contributor
    Join Date
    04-05-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    360

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    I had changed the code you noted to this:

    Please Login or Register  to view this content.

  18. #18
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    See attached a remake with your columns selection in sheet "Duplicate"
    Pay attention to WkCol which is now Public
    Attached Files Attached Files

  19. #19
    Forum Contributor
    Join Date
    04-05-2013
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    360

    Re: Finding specific cell values within multiple worksheets and checking for duplicates

    That worked! Thank you very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Finding Duplicates Across Multiple Worksheets in Excel
    By MSafir in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-15-2017, 07:37 PM
  2. [SOLVED] Finding the minimum value of maximum values from multiple, changing worksheets
    By kwhelanne in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-05-2014, 05:17 AM
  3. Checking a single cell for multiple values
    By xxchurch in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-27-2014, 11:18 PM
  4. Replies: 0
    Last Post: 09-18-2013, 08:00 AM
  5. Finding (numerous) specific values through a number of worksheets
    By hungryheart08 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-18-2013, 06:52 AM
  6. Replies: 3
    Last Post: 02-25-2013, 08:11 AM
  7. Finding and Deleting values in specific worksheets
    By dummyinexcel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2011, 10:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1