A while back I got asked about a way to utilize Excel to compare data from two separate sources or locations - WITHOUT having to painstakingly look through each line manually. If you are manually comparing data across spreadsheets right now, this is especially for you!
In this short video I use a couple of Excel tools to compare two spreadsheets and find what data is missing from one spreadsheet but exists in the other.
Here's the setup - one spreadsheet is a list of patients that have been discharged and the other spreadsheet is a list of patients that have been sent bills for their visit. These lists have about 75,000 visits on each one and 1,000 of them have not been billed.
Before the HIPAA police gets all excited - this is not a list of names, it is a list of randomly generated numbers that I labeled "account number" which represents a patient visit. All made up data. :)
END OF DISCLAIMER
Ok, where was I? Oh yeah - If I wanted to manually compare both lists to find the "missing 1000" I would likely have at least one birthday before I was done. And I'd probably make a bunch of mistakes. Double yuck.
Watch the video, follow the steps and you could do this same comparison yourself in a couple of minutes. Yeah, not a typo - you can find 1000 "needles" in a 75,000 line item "haystack" in a couple minutes!
If you are able to put this quick tip to work, let me know all about it in the comments below!
Imagine this – your business has a critical issue to resolve (if you have a burning issue right now – think about that). If you are like a typical organization, a meeting will be called with a half dozen or more people to “discuss” the issue and course of action. What’s too bad is these “discussions” typically involve one or two people sharing their opinion (sometimes rather loudly) and the rest of the people listening passively and agreeing. Raise your hand if you have ever been in this meeting before. Ok, now put your hand down.
My Opinion is Louder Than Yours!
Opinions are great. They are an essential part of an improvement effort. But, when decisions are solely based on opinions and especially when they are based on a limited number of opinions, it can lead a team astray. Teams that operate this way fall into the trap of confusing activity with improvement. Sort of like rearranging the deck chairs on the Titanic. It doesn’t matter – this ships going down!
Sure, finding the right data can slow things down a bit, but...
There is a difference between obvious decisions and ones that need more data to point in the right direction. I don’t think anyone would need to collect data before replacing a roll of toilet paper on the hanger. You can see the bare cardboard tube; you know it is gone. That’s all the data you need. Hang the new paper.
But when someone is making claims like, “this happens all the time” or “it’s gotten really bad” – that’s your indicator. Does it happen all the time? Like, every single time? Most of the time? A lot this week?
Translation: If the problem is obvious and the solution is simple - just fix it. But, if the problem is vague and you’re not really sure - go grab some data.
Wouldn't it be nice to know before we start trying to fix it?
When I hear these indicator words (always, never, a lot, quite a bit, etc.) I like to use what I call the 12-15 guideline. This method provides a measurement baseline that is quick, simple, and effective at providing a general idea of past performance.
Performing these simple steps will make it easy to see any trends or outliers, and understand the magnitude of the problem. (Check out the helpful links at the bottom for assistance on steps 2 and 3.)
But what if there is "no way" to measure your problem? Think about it. There is a way that data can describe the problem you are experiencing. Besides, you don't want to pour a lot of effort into something you "think" is a problem only to "not really know" if things are better once all of the action is complete.
Some helpful links:
Using the Average Function In Excel: https://support.office.com/en-us/article/Calculate-the-average-of-a-group-of-numbers-e158ef61-421c-4839-8290-34d7b1e68283
Creating a Time Series Plot In Excel: https://www.youtube.com/watch?v=SAgldvOtrhI
Thinking of Outsourcing your Internal Audits?
Christopher M. Spranger, MBA, ASQ MBB
Want to receive free tips to improve your business?