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
developer2966Asked:
Who is Participating?
 
teebonConnect With a Mentor Product ManagerCommented:
Hi developer2966,

Based on the requirement and the excel provided, I don't find the formatting of the excel sheets (PO, RO) suitable for the processing. It is not impossible but it is going to be tedious and prone to errors.

I would recommend the same business process to be recorded in MS Access and the same process would be pretty straightforward.

On a side note, I personally think that 500 points does not match the amount of effort required to produce the solution requested.
0
 
developer2966Author Commented:
I think perhaps using Access will require me to import the excel sheets into the appropriate columns.
0
 
teebonProduct ManagerCommented:
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.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
aikimarkCommented:
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?
0
 
aikimarkCommented:
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.
0
 
developer2966Author Commented:
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!
0
 
teebonProduct ManagerCommented:
Hi developer2966,

I'm glad that it works for you :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.