[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 640
  • Last Modified:

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
0
developer2966
Asked:
developer2966
  • 3
  • 2
  • 2
1 Solution
 
teebonCommented:
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
 
teebonCommented:
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
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
 
teebonCommented:
Hi developer2966,

I'm glad that it works for you :)
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now