POReceived, to the right, is a list of purchase orders and their return date, meaning the job is done - these are closed purchase orders.įigure A These two data sets in Excel represent a simple purchase order system.Īs is, there’s no reconciliation between the two Tables. PODistributed, to the left, tracks the purchase orders distributed to staff - the open purchase orders. With most systems online now, the paper purchase order is a thing of the past, but it provides us with a simple example.įigure A shows two Table objects. You can think of the closed list as a subset of the open list. I’ll use the term open to describe a purchase order in play and closed to describe a completed purchase order. When the employee fulfills the request, they complete the purchase order form and return it to the system. When a client calls and makes a request, you grab a blank purchase order, fill in the details and move on to satisfying the request. If your company supplies ongoing support, you’re probably familiar with the purchase order system. TechRepublic Premium editorial calendar: IT policies, checklists, toolkits, and research for download Meet the most comprehensive portable cybersecurity device Learn the Angular programming language to build your own app This scanning pen translates into 112 languages What’s the problem we’re trying to solve in Excel? TechRepublic Academy For your convenience, you can download the demonstration. I’m using Microsoft 365 desktop on a Windows 10 64-bit system, but you can use any version and Excel for the web supports VLOOKUP(). In this tutorial, I’ll show you how to use VLOOKUP() in Excel to find missing records in one data set when compared to a comprehensive list. Fortunately, it’s not as hard as you might think. SEE: Microsoft Excel: Become an expert with this online training (TechRepublic Academy)įor example, in Excel, you want to compare a set of incoming purchase orders to a comprehensive list of distributed purchase orders. But what if you want to compare two sets of data in Excel to determine if a record is missing from one of the sets? Sometimes the solution finds duplicates across one or more columns sometimes the solution focuses on finding unique values. In Microsoft Excel, I have used many different solutions to compare lists. For more info, visit our Terms of Use page. This may influence how and where their products appear on our site, but vendors cannot pay to influence the content of our reviews. We may be compensated by vendors who appear on this page through methods such as affiliate links or sponsored partnerships. If you need to reconcile batches of records in Microsoft Excel, this simple VLOOKUP() solution offers a quick and easy way to find records in one batch that are missing from another. How to find missing records using VLOOKUP() in Microsoft Excel
0 Comments
Leave a Reply. |