Reconciliation Macro

**halmos** · 07-26-2015, 02:23 PM

Hello,

I am trying to automate a rather mundane task of reconciling past data with new datasets. I have attached a sample of what I am trying to do.

Step 1: Take two different ranges as input, past data and new data.

Step 2: Intermediate - What I am doing currently. Sort the data in ascending order w.r.t. Col B because that remains unchanged between the two datasets. Then using If statements to check the two ranges, highlighting changes in yellow and new data in red.

Step 3: Output - Output only the rows with any new data or changes.

I have written the following code so far:

Please Login or Register  to view this content.

I have also recorded a sorting macro that sorts the two ranges.

Issues where any help would be much appreciated:

1. Combine the sorting with Reconciling so that only one macro needs to be run.
2. My macro consists of hard coded numbers e.g. j+5, the data can have widely varying ranges so I need to figure out how to account for this. There is a blank line between the two ranges.
3. Also I want the new addition to be colored red. This code does not account for new additions but highlights rows in yellow in both ranges leading to a whole empty row highlighted in the past range.
4. Remove rows that have no changes and output the rows that have changes or new data in a new sheet.

Any help would be highly appreciated.

**Richard Buttrey** · 07-26-2015, 02:56 PM

Hi,

It's not immediately apparent to me why you need a macro. If this is a comparison task then normally straightforward functions would be sufficient.

However it's not clear exactly what you are comparing so it's difficult to comment. Also I suspect your data is somewhat simplified from your actual data. If so we like to see samples of the real data since experience here tells us that putting together solutions for non representative data often doesn't work.

If you could show and describe exactly what data you start with and what end result you want, clearly explaining what rules you are applying then maybe we can come up with some standard functions that will produce the results you want.

**halmos** · 07-26-2015, 03:31 PM

Hi, thanks for your prompt reply. Unfortunately, because of the sensitive nature of the data, I can't post it here. But essentially, I have two files, one containing 2015 data and one with 2014 data for different customers. One customer has varying number of entries, anywhere from 5 to >1000 rows. So for Customer 1, lets say there are 10 rows in 2014, and 13 in 2015, this means 3 new data points have been added since old data can't be removed. The sample is a simplified version of the data which contains around 100 columns of variables. The only variable which can be used for comparison lies in column H, hence I sort the data and then compare it.

One variable, for example, is: Date of Birth. If this changes, then I need to highlight both old and new entry in yellow as there is an error in the data. If a new data point has been added, I need to highlight the entire row in red. This makes it easy to see at a glance what has changed and what adjustments need to be made for the next stage of analysis.

I was hoping for a macro because it would be very simple to run it on datasets as and when required because new data arrives all the time, but if a function achieves a similar objective, that would be great.

The attached file shows exactly what I want. I copy and paste data for a customer from both files to get the Input exactly as shown but with 100+ columns of different variables. The output is what I am currently doing: Adding all rows which have data that has changed or rows with new data. So for example if a customer has 10 rows in 2014, and 13 in 2015. And 2 rows have some variables that have changed in value over the year, then my output would have 7 rows in total: 3 new rows highlighted completely in red, showing that these are new data points. And 4 other rows, 2 from 2014 and 2 from 2015 highlighting in yellow what has changed since last year.

I hope this makes it a bit clearer as to what I require.

Thanks.

Reconciliation Macro

LinkBack

Thread Tools

Rate This Thread

Display