Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Fastest way to compare two lists in Excel (VBA)

Options
  • 01-04-2006 2:21pm
    #1
    feylya
    Moderators, Music Moderators Posts: 23,361 Mod ✭✭✭✭


    I have two columns on two worksheets that I need to compare. If an item on the first list appears on the second, I want to copy a value from the first sheet to the second. Anyone know what's the fastest way to do that? I've thought about running 2 for loops to check each item in the first list against all the items in the second but that would end up being 25,000,000 checks which is a bit much...


Comments

  • Options
    #2
    Seth Brundle
    Moderators, Politics Moderators Posts: 39,809 Mod ✭✭✭✭Seth Brundle


    http://www.boards.ie/vbulletin/showthread.php?t=146064 (IIRC)


  • Options
    #3
    cyberbob
    Closed Accounts Posts: 82 ✭✭cyberbob


    it'd be simpler than that thread suggests to set up a hidden sheet with some vlookup(blah,blah,blah,false) and use iserror() and and() to check for #N/A's

    Not very elegant and slow for large numbers of rows (with automatic calculation), but that VBA prolly isnt much (any) quicker..


  • Options
    #4
    Hagar
    Closed Accounts Posts: 16,793 ✭✭✭✭Hagar


    There is a vlookup function built into excel.
    Column a, (the leftmost) must be sorted.
    Click on "fx" and search for "compare number with another column".
    If you use this function in conjunction with an "if" statement you will get a true or false for "found" in column b


  • Options
    #5
    Torak
    Closed Accounts Posts: 80 ✭✭Torak


    feylya wrote:
    I have two columns on two worksheets that I need to compare. If an item on the first list appears on the second, I want to copy a value from the first sheet to the second. Anyone know what's the fastest way to do that? I've thought about running 2 for loops to check each item in the first list against all the items in the second but that would end up being 25,000,000 checks which is a bit much...

    Three options
    1) Use nested for loops; - performance may be crap depending on the size of the lists, but it's probably not going to be too bad.
    2) Sort the data if possible, bring it in to memory and use a binary chomp to find a matching value quicker.
    3) Write a hash table and a hash function if performance is key and this is something you will have to do multiple times.

    If you only have to do it once then the development effort is not worth it and use the nested loops.

    If this is going to be used again and again then either sort the data, or write the hash table, because it will pay for itself ten times over

    http://oopweb.com/Algorithms/Documents/Sman/Volume/HashTables.html has a hash table implementation in VB at the bottom of the page.


  • Options
    #6
    feylya
    Moderators, Music Moderators Posts: 23,361 Mod ✭✭✭✭feylya


    Thanks guys. In the end, I just went with nested loops.


  • Advertisement
Advertisement