(SOLVED) Reconcile two sets of data

**damilaredavids** · 12-30-2011, 08:02 AM

Hello all,

I would like to achieve a reconciliation of employer deduction report aka ('Sheet A') with company deduction report ('Sheet B') line by line. Here are the things i would like to achieve with this:

The reconciliation should reveal any change in any of the rows under the various headers so i need a macro or formular that can pick each of the cells in each worksheet and compare with the other cell in the other worksheet. Any row in 'Sheet A' that matches a row exactly in 'Sheet B' should return a remark "reconciled" in the "Remark" field. Any row in 'Sheet A' that does not have an exact match with any row in 'Sheet B' should return a remark "Not Reconciled" in the "Remark" field.

I am hoping the solution can also create a new worksheet and put the two sheets side by side and match the reconciled rows and leave the unreconciled rows as an exception report sorted at the bottom of the sheet.

Please find attached a 'test' workbook. I have altered some data in 'Sheet B' as a control for the result of the solution.

I will be grateful for a solution as this thing takes me weeks to reconcile and really affects my job.

**Fotis1991** · 12-30-2011, 08:22 AM

Hi and welcome to the Forum.

If i understoond well your point, then in Sheet2 in F2, put the formula and drag down as you need.

Please Login or Register  to view this content.

Hope to helps you.

**damilaredavids** · 12-30-2011, 08:50 AM

Thank you for your prompt reply.

I insert the formular but it keeps returning an error and does not even show any value.

Kindly advise on what I may be doing wrong.

Regards.

**Fotis1991** · 12-30-2011, 09:06 AM

Pls, take a look to the attachement..

Is this, what are you looking for??

**damilaredavids** · 12-30-2011, 09:59 AM

That worked really great...

Thanks for the time you put in.

I just noticed that it stopped in line 13 and that was because there was duplication of rows and hence the remaining rows returned 'Not Reconciled'. Can we find a way for it to pick duplicates separately or for the solution to check a range instead of matching row by row? The essence of this is in cases where the two sets of data are not sorted equally or if there are many duplications in data of about 10,000 rows.

Thanks in anticipation.

**Fotis1991** · 12-31-2011, 04:59 AM

Hi damilaredavids

The formula compares row by row the two sheets and if All cells of the row are sames gives the result.

So from row 13 and then the rows, does not includes the same values so the result is "not re...."