Forum Discussion
INDEX MATCH Duplicate Match Values with Unique Data
It is not necessarily true that unique identifiers need to be created each time, but that the identifiers won't always match. So in one building and in one machine EACH transaction in a billing cycle would show in the unique identifier as I couldn't see a way to create a unique ID otherwise. So for example if machine 15890 had 60 transactions in a billing cycle there would be 15890, 158901, 158902 all the way up to 1589059. Essentially just being the machine number and a number identifying the count of how many times that machine appeared on a report. The problem lies in that the number of transactions between billing cycles will likely never be the same so the identifiers have to be copied each cycle into the corresponding spreadsheet in order for no data to be missed. I'm sure it's possible to come up with a better system here.
What I meant by my goal is I want to be able to copy the new report we pull from a program each billing cycle into my data dump sheet that all the other sheets pull from and then be done with it. It will be used by people with basically no excel experience, but is a requirement of our client and the manual reporting is an 8 hour process. I refer to it as a data dump because I literally just copy and paste the data so no editing is required to get the workbook to function.
It's one customer, but thousands of employees who are not limited to one machine. They will use most of the machines and have many transactions each in a given billing cycle at their building but ALSO are able to access machines at other sites if working there.
The account managers at each building get a full report of items and item number, quantity, price, who dispensed the item, their employee number and their department. I have no problem really with pulling that information from the data dump with index match.
ShockTherapii I have to think it's significant that nobody else has chimed in with a neat solution; there are people on this site who are far more experienced than I with the newest capabilities of Excel. I retired 17 years ago so am most conversant with the capabilities it had at that time (and still does) but I'm realizing in the last week (beginning when I discovered this site) that Excel has added even more amazing powers since I last read the manuals. For one example that I have yet to explore--I've ordered a recent reference manual to help--I see people here talking about Power Query, and I suspect that might have a way of meeting your deeper need. But I'm also quite sure--well, I have a strong suspicion--that what Power Query does is accomplish in more user-friendly fashion what the older database functions do to this day.
Let me describe a solution briefly here that uses Excel's database functions. See if it resonates in concept with what you are doing. If it does, I'd be happy to dig deeper. So here's what I have working currently to support my wife in her consulting business. She has two clients, does similar kinds of work for each, with some distinctions (different categories of projects). Has a need to prepare a monthly invoice to bill those clients, and it needs to show a moderate level of detail: for each client, she will show a list of up to 10 or 12 of the different types of projects, and the total number of hours spent on each of those types or categories during the month. The invoice then applies an hourly rate to the total hours and shows that as the invoice amount. That's the output needed.
At the input end of things, she keeps track of all of her hours in a single database (key point here: there's no need to separate the database into two just because there are two clients; you just have a code in each record that indicates which client these hours were for), noting in each row of that single database:
- Date
- Project category/description
- Hours (can be fractional)
- Client
More data elements could be added to each row as needed. For example, depending on the level of detail expected (itemization on the invoice), one could show sub-projects. We don't need that; my point is that more granular data could be recorded if necessary.
I should add that there's a check for data integrity: the entry under Project Category is validated (Excel can do that) so that she can't inadvertently misspell and thereby have an entry missed in the final report.
The invoice is created by a series of data extraction queries that take advantage of the Excel functions known as database functions. Here's a sampling:
- DSUM: will retrieve the total of a specified column from a database, for all rows that meet a set of criteria that can include 2, 3, 4....a large number of specs. In our case, it's the number of hours for each ProjCategory for each Client for the current month (i.e., three identifiers)
- DCOUNT: counts the records that meet the given criteria
- DMAX: will retrieve the highest number from a specified subset of the database
- DMIN: retrievs the lowest number
- DAVERAGE: I think you're getting the idea
The key in all this is alluded to above in italics, because I think it sounds as if your "unique identifiers" are serving a similar purpose--but you're doing it by creating a whole new data entry in each row, and then somehow collecting those into each of your "data dumps." When in fact, what I'm suggesting is that you could just specify the three (or four, or five) fields that constitute your "unique identifier" in what these database functions call on as the criteria range.
- And unless I'm totally mistaken, you could be putting all the data on all nine spreadsheets into one single database for even more efficiency. Separating them made sense in the old days when the process was paper based....but rethinking it in more contemporary database frameworks makes it possible for you (I'm suggesting, based on what you've described) to just add an identifier to each row of a single database that reflects which building/which machine is in use.
- So let me suggest you go look at Excel's D_____ database functions, get a good description of them, play around a bit with them, and see if they could possibly meet your needs more simply than what you've been doing. I'd be happy to engage more with you if, as I said, any of this resonates as even "possible."
- I think you're really onto something here and I am going to look into those database functions in great detail.
I do want to make one distinction that perhaps I wasn't very clear on though. I only have one data dump which is a very large sheet of ALL the data for every machine and building. The 9 sheets are unique to each building which is required as each building does its own billing based on what their usage was. That path was taken as the billing managers didn't want to see other sites information mixed in with theirs.
I don't think this is an obstacle for the solution you proposed, though. I'll have to copy the database formulas into each sheet, but that's not a problem as all of the formulas will reference information contained in just one data dump sheet.
Thank you for your input! I will respond when I've tested these things out to let you know how it went.