PDA

View Full Version : [SOLVED] Compare data between two sheets



Kartyk
03-13-2017, 12:21 AM
Hi All,

I have been trying to complete a macro for long time now. Last request reached dead end and I am still stuck with it.
Heres what I was looking for -

A code to compare two sheets and validate if it is a Pass or a Fail.

"Account" on either of the sheet is the primary key. So step 1 is to check of the account column is the same.
If yes, then match other columns and even if one of the column doesnt match, then mark the last column as "Fail".
Should every detail match then mark as "Pass".

There is one exception though,

Column "Lots" might have one row matched to many as there could be summation involved. For eg. Sum of 3 rows, on one sheet,
might add up to the one row on the other. Code should factor this in.

Also, some of the requirements are

1 Columns can be anywhere in both the spreadsheets - Hence it has to be dynamic
2 There can be multiple columns on the sheet and columns to compare are only listed in the sample sheet.
3 Data compare can be between numbers, date or text.

Thanks a lot in adavance.
Any questions or clarifications, please respond.

Cheers
K

Kartyk
03-16-2017, 12:35 AM
Hi,

Any luck with this request please ?

Cheers
K

mdmackillop
03-16-2017, 02:59 AM
A code to compare two sheets and validate if it is a Pass or a Fail. - OK
"Account" on either of the sheet is the primary key. So step 1 is to check of the account column is the same. - OK
If yes, then match other columns and even if one of the column doesnt match, then mark the last column as "Fail". - Where?
Should every detail match then mark as "Pass". - Where?
There is one exception though,
Column "Lots" might have one row matched to many as there could be summation involved. For eg. Sum of 3 rows, on one sheet,
might add up to the one row on the other. Code should factor this in. - Give us an example
Also, some of the requirements are
1 Columns can be anywhere in both the spreadsheets - Hence it has to be dynamic - OK
2 There can be multiple columns on the sheet and columns to compare are only listed in the sample sheet. - What sample sheet?
3 Data compare can be between numbers, date or text. Give us an example

Please provide a better example showing a desired results for differing scenarios.

mdmackillop
03-16-2017, 03:15 AM
I see Leith Ross has spent considerable time on this here (http://www.vbaexpress.com/forum/showthread.php?58584-Recon-between-two-spreadsheets-across-various-columns) but you have provided no link to his work or acknowledgement. I'm not inclined to assist further.

Kartyk
03-16-2017, 03:30 AM
File attached on my request has the examples and also the code that was left-off by Leith Ross. I did mention that request had to be closed, since it reached dead end. Hence the new request.

Regards