Why do the lists change daily? It sounds like you’re producing a file directory. List A is the master list, entries on which can be found at one of three locations corresponding to your mini-lists in CDE. You’d also like to know if any of the entries on A are completely missing from CDE.
I can see why these would change if you have finished compiling CDE, in which you should just wait until CDE are finalized. Or the process is more subtle than all that. What is it that you want your Excel database to do, and what raw materials do you have to do that with?
Chessic Sense, what order are your sub-lists sorted in (e.g, by jersey number or by name or something else)? You said they had to be maintained in that order. Are new ones just added at the end, or might a new entry be stuck somewhere in the middle?
I’m also curious if Excel is a requirement, or if it’s just what you’re familiar with. There are some other software options that might be easier for this type of project.
Is this a one-time project that’s dragging on due to the incoming data issues, or an ongoing new system? If it’s going to be maintained indefinitely, then Kimmy_Gibbler has some valid (if poorly expressed) points, and it would probably be worth the time to look at other software possibilities and design.
If it’s just a one-time deal, then it’s probably not worth the investment. As long as you’re getting accurate results, fastest and easiest is the way to go.
We have a file tied to a serial number. Each day, we open up some (however many we can get through) and do some work with it. Then we check it off the list. We want to track it all in SharePoint. These files exist in two places- attached to a record in a separate system’s Sharepoint (which is old, and modified poorly for our purposes) and in a windows folder. Our end state is twofold: We want a master list that’s easily searchable of all the ones we’ve worked on and that we will work on eventually (so the other teams don’t touch them and duplicate our work). We also want them all in Sharepoint so we can have other data points besides the serial number sortable, filterable, etc.
The first step has been solved. I just made a directory list of the files in the windows folder. Now we have a master list. This list may or may not match the low-side’s Sharepoint records. Hopefully, it does.
C is already in sharepoint. One problem is that we don’t have all of D and we don’t know any of E, unless we did it manually through all 7000 of them, which we’d done so far for C and half of D til I saved the day and automated it. Go me. The low side knows what the lists are, but we can’t tell the high side what they are since they don’t communicate with each other.
The original task was “Hey, now that we have this master list, can you figure out which ones are C, D, and E, so that we can automatically import E?” It matters that they be kept separate, you see, so we know what to work on next. My idea was to build this program so that as D comes in, we can subtract that off of the master list so that it’s in E. That can then be proofed later on.
Does that makes sense? Any questions?
Excel is not a requirement, and it’s mostly a one-time deal. That is, we have most of the data and we’ll be working on it for the next 10 months-ish. A new guy would be added to the bottom of the list, but there really shouldn’t be new guys- pitchers can become managers though, for instance. CDE are sorted by date-time stamp.
So why do you say the lists change daily? It sounds like a given file’s membership in C, D, or E is a fixed property. Moreover it sounds like you have a full list for each column and a list A which should be the union of C, D, and E. Is this correct?
A given file’s membership is mostly fixed. List A is the union CDE, but we don’t know what D and E are. We know half of D and we learn more every day. E is unknown unless we go through a painfully slow, manual process of looking them up and crossing them over. That’d take roughly 350 man-hours. What we want to do is maintain a list A that represents what we have and lists CDE that represent what we should have. In theory, those lists are the same but there’s maybe 100 or so that aren’t in A. But we’ll deal with that later. It’ll show itself over time anyway.
Right now, we want to use A to figure out what should be in E. I wanted a function that would mark off A as more of D came in. Once we get all of D, we can assume that whatever is left in A belongs in E. We’ll then be able to make the 7000 sharepoint records automatically instead of manually, as the team had done for 3 or 4 months before I fixed it.
I think we’ve got what we need. My boss appreciated it. I said to a coworker that I haven’t gone through many records today. He said “Well you’ve been Excel-ing all day”. My boss chimed in “He’s been excelling all week.” Pun intended. And I have you guys to thank for it.
Yeah, sounds like what you’ve got is working well for the situation you’ve got to deal with. Sometimes the “best” solution, isn’t.
Don’t forget to be sure your file is recalcing all the formulas when you add new data. (I think it defaults to that, but if you’ve got it turned off then your setup won’t work.)
Good luck and glad we could help!
I’ll note my work for National Security so I can use it on a resume. Of course, I won’t be able to tell them what it was, or I’d have to kill them. 