In VBA(in Excel) how can use a query to compare two recordsets?

[Sorwen]

I'm trying to create a faster way to compare data from one sheet to another in Excel. The way I currently do it is assign the data to variables and then compare the variables. This works fine on small amounts of data, but bogs down on larger sheets. I figured the easiest way to do it would be to assign the data for one page to a recordset and then assign the other page to another recordset. After some help in understanding how to do this in excel vs. how I knew to do it in access I can set the recordsets fine an query them. What I don't know how to do is query them both to compare the data. I know the SQL for it, but I don't know if there is a VBA command that will allow me to open and query both recordsets at the same time. Is there away? I can't import it into access and then query there I have to do this in Excel.

 

[SkipVought]

Hi,

How many fields (columns) are we takling about?

Do rows correlate or can the data in row 1 correlate to the data in row 10?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[Sorwen]

One column at the moment and the column. Data in row 1 can correlate to data in row 10, 15, 255, 65000, or in all 4.

 

[Sorwen]

Need to remember no edit buttong so be more careful. That should read One column at the moment and the columns are the same on both sheets.

 

[SkipVought]

Are they in the same workbook?

Just use MS Query to join the 2 table (sheets) on whatever fields correlate.

Could use an outer join to see what rows are in one and not in the other.

However, I often use

=MATCH(A1,Sheet2!A:A,0)

to see what values are not in my other table (sheet)

What information are you looking for? What's in one and not the other and visa versa?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[Sorwen]

Basically what happens is someone who doesn't know much about Excel imports data from another source into two different sheets. They then need to know what is found on sheet1 and sheet2 and then mark those rows that are the same on sheet2.

 

[SkipVought]

The MATCH function does that quite well. #NA return means no match.

What's the process, though? I'd use this for ad hoc verification. Are you opening other user's workbooks, looking for this? Is this a one time thing or on-going? When does it occur? How often?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[Sorwen]

It is on-going and will be used by multiple users with little to no Excel experience. Then after they do what they need to with the results it maybe given to others as well. My current one matches on several columns and you may be checking sheet1 to sheet2, sheet3 to sheet4 then sheet4 to sheet2 depending on the why at the time. There is no telling when or how often as that varies. The lines are usually marked various colors depending on the what and why at the time.

It does all of this now and more, but because I read the data from the column on both sheets, then check the data from one sheet to the next, then mark that data for each one that is the same with the columns and rows never the same from one check to the next. It is all very slow.

This is all put into an add in that all I have to do is put the add-in on their machine and have them press a few buttons (customer forms) and then they are done.

 

[Sorwen]

Custom forms not customer.

 

[SkipVought]

So you mark these columns in various colors? Then what? What is done with this information?

You have a very undisciplined situation it seems. "My current one matches on several columns and you may be checking sheet1 to sheet2, sheet3 to sheet4 then sheet4 to sheet2 depending on the why at the time. There is no telling when or how often as that varies." Hard to progam for.

This whole thing is not very clear on the PURPOSE and OBJECTIVE.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[Sorwen]

That is because it can be different everytime. Thats why I kept the original explation simple as two sheets compared to each other by one column and the results are colored. That is were I started when I built it and built up from there.

 

[SkipVought]

I'd do it with MATCH and Conditional Formatting.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}