473,322 Members | 1,379 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

A good algorithm for this specific problem

Hi all
I am having a question about Excel VBA
Hope that this is the right place to ask

Now i am having 2 giant spreadsheets, one has about 2000 entries, another one has about 1000 entries. The 1000 entries one contain information that i want to match it with the 2000 entries one

So, i came up with the code that for every entry in the 2000-entry file, i check with every entry in the 1000-entry file, see if there is a match

but then , the running time will be: 1000^2000 times, which is too much and not efficient

I know it is possible to use Vlookup function, but what if there is duplicate value in the 1000 entries and i only need the maximum number (assume that the information i need is numeric) ?

if you can provide a way to use Vlookup, that would be great
and a way / algorithm for VBA would be awesome. And a small pieces of the sample code would be great.

I was thinking about that, as soon as you find the entry in 1000-entry file, take the maximum value, and for the next run, just ignore all the duplicate of the last one. so the 1000-entry will no longer be 1000 entries after each run but less.

assume that we are working with different worksheet in the same workbook
thank you very much
Feb 23 '10 #1
2 1522
For every record in A (1000), check every record in B (2000) => 1000 * 2000 not 1000^2000

if the check is something like A.attr1 = B.attr2 then this is exactly what a SQL-JOIN does (very fast)
Feb 23 '10 #2
ADezii
8,834 Expert 8TB
Here is a code snippet that I wrote for a User that compares every Value in Sheet1 within the Range $A$2:$F$1000 to every Value in Sheet2 for the same Range. This is a very CPU intensive process, since it involves approximately 36,000,000 iterations of the Nested Loops (approximately 2 minutes). This should be a good start.
Expand|Select|Wrap|Line Numbers
  1. Dim rng_1 As Range
  2. Dim rng_2 As Range
  3. Dim rngRef_1 As Range
  4. Dim rngRef_2 As Range
  5.  
  6.  
  7. Set rng_1 = Worksheets("Sheet1").Range("$A$2:$F$1000")
  8. Set rng_2 = Worksheets("Sheet2").Range("$A$2:$F$1000")
  9.  
  10. For Each rngRef_1 In rng_1
  11.   For Each rngRef_2 In rng_2
  12.     If rngRef_1.Value <> "" Then
  13.       If rngRef_1.Value = rngRef_2.Value Then
  14.         Debug.Print "Value " & rngRef_1.Value & " in Sheet1 found at " & _
  15.                      rngRef_2.Address & " in Sheet2"
  16.       End If
  17.     End If
  18.   Next
  19. Next
Feb 24 '10 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

17
by: savesdeday | last post by:
In my beginnning computer science class we were asked to translate a simple interest problem. We are expected to write an algorithm that gets values for the starting account balance B, annual...
6
by: trentdk | last post by:
I want to test which language (testing C and FORTRAN) would be faster with math calculations; one test with intergers, and another test with floats. What math formulas/functions would you guys use...
5
by: poison.summer | last post by:
I have a line of binary codes such as 1 1 1 0 0 0 1 1 1 I'd like to change the number of consecutive 0s to be even. is there any good algorithm in C? Thanks
113
by: Bonj | last post by:
I was in need of an encryption algorithm to the following requirements: 1) Must be capable of encrypting strings to a byte array, and decyrpting back again to the same string 2) Must have the same...
4
by: zing | last post by:
Our company is in the startup phase of a large project involving lots of network traffic. At this point, I'm trying to find out whether TCP will be fast enough for the task. I've read a few...
26
by: vlsidesign | last post by:
I am a newbie and going through "The C programming language" by Kernighan & Richie on my own time (I'm not a programmer but I want to learn because it can save me time in my normal job, and it is...
206
by: WaterWalk | last post by:
I've just read an article "Building Robust System" by Gerald Jay Sussman. The article is here: http://swiss.csail.mit.edu/classes/symbolic/spring07/readings/robust-systems.pdf In it there is a...
1
Ganon11
by: Ganon11 | last post by:
Hey guys, I'm back, and with another FUN question! My latest homework asks this question: "Suppose all the edge weights in a graph are integers between 1 and |E|. How fast can Dijkstra's...
76
by: lorlarz | last post by:
Crockford's JavaScript, The Good Parts (a book review). This shall perhaps be the world's shortest book review (for one of the world's shortests books). I like Douglas Crockford (because I am a...
1
by: almurph | last post by:
Hi everyone, Concerning the Needleman-Wunsch algorithm (cf. http://en.wikipedia.org/wiki/Needleman-Wunsch_algorithm) I have noticed a possible loop. Inside the algorithm there is an...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.