I have the following problem (simplified):Data are stored into two files (Today and Yesterday) of same structure with 2columns: ISIN and Price.I need to find price change between today and yesterday.Speaking in SQL-language it looks likeSELECT t.price-y.price FROM t, y WHERE t.ISIN=y.ISIN;Limitation: everything must be done with Excel/VBA (I have other ways, but they involve mixture of technologies which I hate)thnx for help if any

Does "only" in VBA mean you can't treat the sheets as a database and use a driver on it ?

How many lines of data - if there aren't many then you could use ADO.What format are the files - CSV, TXT or maybe XLS files?If you wanted to do it in VBA, and there aren't many lines, it's pretty straightforward. A good start is to let excel sort the data for you first, then you can just cycle through each unique ISIN in your "today" list, and look for the changes. though having said that, if there aren't many lines, you might as well just do a vlookup in excel.

data can be either in memory (VBA) or in cells. it does not matter really, but i prefer to do all job in-memory, not in cells.number of lines with unique ISIN ~ 10,000I know how to create ADO objects from this data and can save info into ADO-formatted file, no problem.The question for me then is how to make join of two ADO-tables?I am completely confused with this microsoft stuff... oracle/java is soooo, much easierthnx guys.

One way you can do it is with DataShaping in ADO. DataShaping is meant for creating parent-child heirarchies, but if you just give one child per parent, you have effectively a join.Here's an example I came up with - note the slightly contrived ways of a) creatign the data shape, and b) getting at the 'child' ie joined item. You'll get a list of 1000 joined items.

to Doney:Waw!!!thanks a lot.... I will take a look. Hopefully, will have no more questions.Brief observation of the code brings me to my usual conslusion: Microsoft invents a wheel... the square one... thanks again.

I suppose it is a dumb question but why don't you want to say something like ?=VLOOKUP(E1,Today_Range,2)-G1Where E has a list of ISINs

QuoteI suppose it is a dumb question but why don't you want to say something like ?=VLOOKUP(E1,Today_Range,2)-G1Where E has a list of ISINs as i said, i prefer VBA-style, rather than in-cell one.there are ~10,000 lines to match.

How is the data stored?

QuoteOriginally posted by: PikeHow is the data stored?in the text file.-----------------after looking solution given by Doney, i decided to use in-cell solution by:- loading info into cells- looking for match from VBA in the following way (roughly)for each mycell in Range("B1:" & nrecB) with Range("A1:" & nrecA) match = .Find(mycell, xbValue) if Not match is Nothing then .... some code.... end if end withnext mycellworks relatively fast (for 1000 records about 5 seconds)unfotunately, i expect the execution time to grow quadratically with increase of number of records for a moment the solution is ok... will see laterthank a lot to everybody

How about using a collection in the manner of a hashtable, try adapting this code.... You will need to catch errors when there is no match.To do 100,000 lookups this way takes less than 1sec on my machine

to Russellmy respect. i never knew such prop. of collections.thanks