+ Reply to Thread
Results 1 to 12 of 12

VBA To Highlight Duplicates against a Master list across multiple columns

  1. #1
    Registered User
    Join Date
    02-19-2014
    Location
    East Anglia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Smile VBA To Highlight Duplicates against a Master list across multiple columns

    Hi

    this is very similar to a lot of threads.

    I have one sheet of consolidated serial numbers over numerous columns, each column has a date in row A and a name in row B, underneath is a list of serial numbers of assets that have been scanned to that person on that date.

    I then get a list of serial numbers and I need to know if that serial has been scanned to an engineer and if so on what date.

    I know a VBA can do this, but the ones I have found don't do the trick... I'm thinking that the VBA should search the consolidated spreadsheet and if it finds a duplicate it can either highlight the cell and I can then manually see who had it on what date, or the duplicate can be copied onto a 3rd sheet (column A), with the Name, (column B) and the Date (column C).

    I can't use "find duplicates" conditionally formatting as other serial numbers may get scanned in and out various times during the month so I get highlighted serials that aren't on my query list.

    Any help would be appreciated.

    Bagpuss
    Last edited by Bagpuss1234; 12-04-2015 at 08:50 AM. Reason: forgot to add a parameter

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,124

    Re: VBA To Highlight Duplicates against a Master list across multiple columns

    If you could attach a sample it would help. I'm a bit fuzzy on the requirements, but maybe this formula could help:
    Match(A2,A:A:,0) = ROW() yields true for the first occurrence of an item and false for the duplicates. You can use the ROW() part of the function to determine what row contains the original.

    I'm sure what you want done can be done easily in VBA, but I'd need to see a sample.

  3. #3
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: VBA To Highlight Duplicates against a Master list across multiple columns

    I too may be able to offer a solution upon submittal of an example worksheet.
    If you are happy with my response please click the * in the lower left of my post.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,796

    Re: VBA To Highlight Duplicates against a Master list across multiple columns

    I can't use "find duplicates" conditionally formatting as other serial numbers may get scanned in and out various times during the month so I get highlighted serials that aren't on my query list.
    If you use "Use Formula" for the rule, you could use countifS() (for multiple criteria) to test with
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    02-19-2014
    Location
    East Anglia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Post Re: VBA To Highlight Duplicates against a Master list across multiple columns

    Hi Guys

    Apologies for the delay in replying, been busy being a mum over the weekend! Attached is an example of what I need.

    1. Issued: This shows the engineers and the date with all the serial numbers of assets that were issued
    2 To Find: The list details all the asset serial numbers that I need to find where they were last seen

    Obviously the Issued sheet would cover a month... or even longer, and the serial to find numbers can run to over 1000!

    Anything to help reduced the mandraulic aspect of this task would be much appreciated!!!

    Many thanks in advanced!

    Bagpuss!
    Attached Files Attached Files

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,124

    Re: VBA To Highlight Duplicates against a Master list across multiple columns

    What do you want to do when the serial number isn't on the list anywhere, or appears more than once in a column?

  7. #7
    Registered User
    Join Date
    02-19-2014
    Location
    East Anglia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: VBA To Highlight Duplicates against a Master list across multiple columns

    Hi Dflak

    1. If the serial number isn't on the list then maybe turn the cell into a differenct colour so I can then sort by colour and deal with them separately.

    2. I really only need to know the last dated occurrence of the asset as this was the last time the asset was seen!

    Many thanks

    Bagpuss

  8. #8
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: VBA To Highlight Duplicates against a Master list across multiple columns

    This will put the name and date on the To Find worksheet in columns B and C. (Note!!!!!! Your example worksheet did not appear to have any matches between the 2 sheets. When I copied and pasted some values from the "To Find" sheet to the other sheet the macro worked just fine):

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    02-19-2014
    Location
    East Anglia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: VBA To Highlight Duplicates against a Master list across multiple columns

    Perfect!!! Stynknts.... the example wasn't my real data, but I ran it on my real data and it works a treat!

    Where do I send the biscuits

    xxx

  10. #10
    Registered User
    Join Date
    02-19-2014
    Location
    East Anglia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: VBA To Highlight Duplicates against a Master list across multiple columns

    hmmm don't know how to mark this as solved!

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,796

    Re: VBA To Highlight Duplicates against a Master list across multiple columns

    use the Thread Tools at the top of the screen

  12. #12
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: VBA To Highlight Duplicates against a Master list across multiple columns

    Glad it worked for you.

+ 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. Replies: 2
    Last Post: 06-30-2015, 04:45 PM
  2. Using filter to highlight/filter duplicates in multiple columns but within 1 day
    By DaveBre in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-25-2014, 04:07 AM
  3. [SOLVED] Highlight Duplicates Between Multiple Columns
    By WorkwearExp in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-10-2014, 08:12 AM
  4. [SOLVED] Returning Multiple Results into different columns from a master list
    By SpoonMan22 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-26-2013, 10:37 PM
  5. Match multiple columns with different values to make a master list of both
    By Niper in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-07-2012, 06:53 AM
  6. Match multiple columns with different values to make a master list of both
    By Niper in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-10-2011, 07:40 AM
  7. Replies: 5
    Last Post: 07-05-2011, 06:25 PM

Tags for this Thread

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