Cut Data Reconciliations by 90%

Picture of two sets of data to reconcile

Cut Data Reconciliation by 90 percent

You get a new data file and the numbers are close, but slightly different from the other file you already had.  Suddenly, you’ve got two different sets of data.  Now you have to spend two hours matching things up.

Okay, let’s NOT do this. Instead let’s use three steps and make this project a ten minute exercise.  Then we can bank that extra time and use some of it towards that side project you’ve been wanting to do.

Step 1: Find the criteria

Ask the person who sent you the data to include the criteria they used to run the report. Ninety percent of your effort will be trying to find this information by looking in the data, but if you ask up front you can save yourself a lot of time.  Many times the issue is simply that each set of data is using different date ranges (fiscal vs. calendar, or up through yesterday vs. including today, etc.)

Step 2: Find the primary data items

Create a concatenation of the “key” fields in each report. So if you’re looking at customer, region, and part, then join those three fields together into one field. Do this for both reports.  Then sort the data by this field.  You can use this list as a unique set of data and that alone may be all you need.  Here’s a video on how to get the unique records in Microsoft Office Excel 2003 (Tip: Office Excel 2007 has a one button solution for this – click on the “Data” Ribbon and select “Remove Duplicates”.)

Step 3: Find the subtotals of the primary data

Create a subtotal for each set of data by the concatenated field. Use this subtotal on each set of data.  This usually reduces the checking from thousands of rows to less than one hundred. If not, you may want to adjust your concatenation to have fewer fields. Once you’ve done this for both sets of data, the differences jump out at you and you can look at the records within that concatenated “key” to isolate your differences.

What other steps should be included?  Share with the community in the comments.

Photo credit: ajturner

This entry was posted in Best Practices, Communication, Excel and tagged , , , , , , , . Bookmark the permalink.

12 Responses to Cut Data Reconciliations by 90%

  1. Terrific work! This is the type of information that should be shared around the web. Shame on the search engines for not positioning this post higher!

  2. Beneficial info and excellent design you got here! I want to thank you for sharing your ideas and putting the time into the stuff you publish! Great work!

  3. pretty helpful material, overall I consider this is well worth a bookmark, thanks

  4. Anonymous says:

    Thanks for that awesome posting. It saved MUCH time 🙂

  5. Thanks for the great post.

  6. Thanks for the great post. Bookmarked

  7. Anonymous says:

    Superb blog post, I have book marked this internet site so ideally I’ll see much more on this subject in the foreseeable future!

  8. maria andros says:

    Hey there this is a fantastic post. I’m going to e-mail this to my pals. I came on this while exploring on aol I’ll be sure to come back. thanks for sharing.

  9. James V Despain says:

    Thanks for the great post. Page Bookmarked

  10. bravedave says:

    Thanks for the great share. Amazing post.

  11. Ashton Bennage says:

    Nice blog post. Keep up the good work.

  12. Doug Shuff says:

    Really love your post.

Comments are closed.