Link to home
Start Free TrialLog in
Avatar of developer2966
developer2966Flag for United States of America

asked on

Match Values in a Column of Two Excel Worksheets using a unique identifier (Reconciliation Exercise)

I have attached an excel workbook which contains the following 5 worksheets:

1. PO
2. RO
3. Results
4. In PO but not in RO
5. In RO but not in PO

I am looking for a macro or small software/script which can take the values in Worksheets 1 (PO) and 2 (RO) and compare the 'TOTAL' column value for a company using its unique code and/or name.

Based on the results of the two sheets a report is to be generated in one of the next three worksheets.

In the 'RESULTS' worksheet if the unique code and/or name, and total value is found in both sheets (PO & RO) then we want to show the values here regardless of the total values being matched or not. Maybe we can show a Y/N value in the 'Total Mismatch?' column. Y for "Yes, there is a mismatch in the total values" and N for "No, both values are equal".

In the following two worksheets it's pretty straightforward. Any unique code, name, and total that is found in one of the sheets and not in the other is to be produced in these two sheets, respectively.

**IF PROVIDING A MACRO THEN KINDLY PROVIDE A STRONG DOCUMENTATION AS IT WILL MAKE UNDERSTANDING THE CODE EASIER**
Rec.-Exercise.xls
ASKER CERTIFIED SOLUTION
Avatar of teebon
teebon
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of developer2966

ASKER

I think perhaps using Access will require me to import the excel sheets into the appropriate columns.
Hi developer2966,

Is the formatting of the excel sheet already fixed?
The issue now is that the row with the FINAL sum does not occur in a uniform pattern.

So, without a uniform pattern, it is also not possible to be imported into MS Access.
You can attach the sheets to an Access database and treat these worksheet contents as tables.  You do not have to import the worksheets.

Is this "reconciliation exercise" for an accounting class or some other subject?
I take back what I said about being about to do this with Access attached tables.  I hadn't looked at the uploaded workbook before I posted.

1. The data is not really row/column data.  It is a report
2. The Excel data doesn't use formulas for the group sums.
3. It isn't clear what rules govern the results of this 'reconciliation' and the 3rd-5th worksheets do no contain exemplary data.
I went with your original idea and used MS ACCESS. First, I ran a macro in Excel to put the data in its proper format for the SQL Query and then imported the excel using the the EXCEL IMPORT feature in Access. After that, I ran SQL Queries and was able to generate the report. It's fantastic!
Hi developer2966,

I'm glad that it works for you :)