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

Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Fastest way to compare two lists in Excel (VBA)

feylya · 2006-04-01 13:21:38

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…

01-04-2006 1:21pm

#1

feylya

Moderators, Music Moderators Posts: 23,363 Mod ✭✭✭✭

Join Date: February 2003

Posts: 21875

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...

0

Comments

#2 01-04-2006 2:20pm

Seth Brundle

Moderators, Politics Moderators Posts: 41,235 Mod ✭✭✭✭

Join Date: April 2003

Posts: 40181

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

0
#3 03-04-2006 7:09pm

cyberbob

Closed Accounts Posts: 82 ✭✭

Join Date: November 2004

Posts: 81

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..

0
#4 03-04-2006 7:33pm

Hagar

Closed Accounts Posts: 16,793 ✭✭✭✭

Join Date: April 2002

Posts: 15106

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

0
#5 03-04-2006 7:48pm

Torak

Closed Accounts Posts: 80 ✭✭

Join Date: September 2005

Posts: 78

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.

0
#6 03-04-2006 7:50pm

feylya

Moderators, Music Moderators Posts: 23,363 Mod ✭✭✭✭

Join Date: February 2003

Posts: 21875

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

0
Advertisement