How can I make an excel spreadsheet log that will put multiple entries under one record

Solution 1:

Your dilemna about organising your data is a common one. In essence, you have two types of data: the first type is the purchase order, whilst the second is the purchase lines within each purchase order.

Purchase order data might typically include information like:

• Purchase order number
• Supplier
• Date raised
• Approver
• Status (Such as waiting to be sent to supplier, waiting for goods to be received, all goods received, matched to supplier invoice, etc)

Purchase lines data might typically include

• Purchase order number
• Line number within the purchase order
• Ordered item code number
• Unit price
• Ordered quantity
• Date goods received

As you have found sometimes a Purchase Order contains only one Purchase Line but sometimes a Purchase Order may have multiple lines. That is the root of your dilemna.

Dependent on what you want to get out of your tracking log (ie the business purposes is it intended to serve) you may need both types of data. If you are only interested in say the financial value of goods on order, then you may only need the second type of data. Generally, though, both types of data will be useful to have and will help maximise the value of your tracking log.

An ideal solution would involve use of database software - the two data types will correspond to tables in the database and can be "linked" (related) together via the purchase order number which is common to both types of data. But if you only have a couple of tables (as in this case), you can achieve a fair amount using Excel.

In Excel, you can use functions like =COUNTIFS(), =SUMIFS(), =VLOOKUP, INDEX/MATCH to "hack" the type of processing that would be undertaken using SQL in a database environment. Be careful and make sure your data is consistent both between and within the two types of data (no mispellings, extra inserted spaces in names, etc). You will undoubtedly come to regret any inconsistencies as the amount of data in your tracking log grows - results will be incorrect and errors hard to find.

Have a close look at what was being said nearly 12 years ago in this question and answer. The first answer is particularly relevant.

Avoid confusing data with presentation of results of that data (or even presentation of the data itself). I touched on this theme in an earlier answer to another question. There is also some helpful advice about some of the principles of basic data management on this page.