VBA Excel: How do I resume matching data at a specific location

[RSJohnson]

I have two worksheets populated with data from different sources. They contain a common data element and the does not occur more than once in each table. There may be omissions.

I compare the elements and when they match I copy some data from the first spreadsheet to the second. When they don’t match on the first comparison, I iterate through the second worksheet’s element until there is a match or if there is no match I record a comment to that affect and move to the next row in the first worksheet and resume the process all over again.

After a no match cycle I need to start the second worksheet at the next data element after the last match so not to look at up to 15K data elements that have already been matched.

Can someone suggest a way to do restart at the next element after the last match?

 

[SkipVought]

semaphore,

This sounds like a table comparison process. I'd use MS Query and use the sheets as data sources.

You have

1. The Intersection - where key fields all match. The shorthand...

SELECT {your field selections}
FROM Sheet1, Sheet2
WHERE Sheet1.Key1 = Sheet2.Key1
  AND Sheet1.Key2 = Sheet2.Key2

2. Sheet1 Key not in Sheet2

3. Sheet2 Key not in Sheet1

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RSJohnson]

Skip,
Microsoft Query was not installed on my machine. I think installed it but I can not find anything called Microsoft Query. Is it "Get External Data" under the menu item Data? If so, MS Excel is not a selection under New Database Query. None of the other option seem to apply.

The code you provided looks like a SQL query, is Microsoft Quesy just SQL by another name?

Do I just write a sub procedure to run the code using Excel normenclature (i.e. Workbooks.Sheets. etc.) to identify the sheets involved?

Am I correct in my belief that the query will act on the whole data source and create list of results. I need the results of each match to go to a location on the same row as Sheet2Key1. I also need to alert the user of instances of no matches.

I can do this with a Do loop and nested Ifs. I can substitute your query for the messy code I am using the match keys now but I still will need the Do loop and Ifs for proper placement of the results, right?

My code starts at the top of the key1 list after a non match and I need to resume the matching at the row below the last match.

 

[SkipVought]

Yes, you do get to MS Query via Data/Get external data...

You MUST have in the Choose Data source...
[tt]
...
Excel Files*
...
[/tt]
That is what you want.

Yes it looks much like the QBE grid in Access, but there are differences (limitations). But you should be able to do a simple inner join on 2 tables in the same workbook (should be on separate sheets)




Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RSJohnson]

Skip,

Excel Files is an option, I just over looked it not once but several times. I will follow you advice.

Thanks very much.