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.
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
I see Leith Ross has spent considerable time on this here but you have provided no link to his work or acknowledgement. I'm not inclined to assist further.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
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.