+ Reply to Thread
Results 1 to 5 of 5

compare two short lists for duplicates

  1. #1
    Forum Contributor stockgoblin42's Avatar
    Join Date
    05-26-2011
    Location
    vancouver, canada
    MS-Off Ver
    Excel 2010
    Posts
    222

    compare two short lists for duplicates

    Hi,

    I have 2 short lists:

    F2:F6 and F7:F11

    and I need to do a quick check to see if any of the numbers in the first list match any of the numbers in the second list.

    Is this possible without an array formula as I'll be putting it in a macro and looping it many times.

    Any ideas?
    Last edited by stockgoblin42; 08-25-2013 at 08:39 AM.
    live logic & long prosper

  2. #2
    Registered User
    Join Date
    02-19-2013
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    71

    Re: compare two short lists for duplicates

    Write a formula in G7 as F2=F7 and drag it until G11. If all produce TRUE that mean comparation is right.

  3. #3
    Forum Contributor stockgoblin42's Avatar
    Join Date
    05-26-2011
    Location
    vancouver, canada
    MS-Off Ver
    Excel 2010
    Posts
    222

    Re: compare two short lists for duplicates

    Hey thanks. I tried:

    Please Login or Register  to view this content.
    copied down and that works alright.

    However: I actually need to delete a block of cells out of there often and that changes the formula that are on another page. Do you know how to get around this?

  4. #4
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    493

    Re: compare two short lists for duplicates

    Have you considered using Conditional Formatting to look for duplicates? No need for formulas, so no unwanted changes in formulas either.
    When I say semicolon, u say comma!

  5. #5
    Forum Contributor stockgoblin42's Avatar
    Join Date
    05-26-2011
    Location
    vancouver, canada
    MS-Off Ver
    Excel 2010
    Posts
    222

    Re: compare two short lists for duplicates

    I need the check in VBA, so conditional formatting can take a leap and turn itself green.

    WOW!

    Does:

    Please Login or Register  to view this content.
    Really produce the number of matches or 0 if none?

    and I guess in VBA it would be:

    Please Login or Register  to view this content.
    Right?

    Darn, that's not right. Excel doesn't like that VBA code. Anybody see the problem?
    Last edited by stockgoblin42; 08-25-2013 at 08:39 AM.

+ 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: 12-27-2013, 09:24 AM
  2. [SOLVED] Short list of values from long list of duplicates
    By Hang Glider in forum Excel General
    Replies: 4
    Last Post: 04-21-2012, 07:13 AM
  3. Looking up values from many short lists.
    By rskim119 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-10-2008, 01:16 PM
  4. Duplicates over two lists
    By mc515 in forum Excel General
    Replies: 1
    Last Post: 04-10-2008, 10:40 AM
  5. [SOLVED] How do I compare 2 lists and show duplicates as a new list?
    By AnaBannana in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-07-2005, 12:06 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