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. DISCLAIMER 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!
3 Comments
Cindy Orzel
4/20/2017 07:00:55 am
This function is extremely valuable and I have used it countless times! You explained it very well!
Reply
Christopher Spranger
4/20/2017 07:34:02 am
Thanks for the comment Cindy! I am glad you found this function and have been able to use. It is SUCH a time saver!
Reply
Leave a Reply. |
AuthorChristopher M. Spranger, MBA, ASQ MBB Archives
February 2021
Categories
All
Want to receive free tips on how to use Lean Six Sigma to improve your business? |