Design - Unique companies in many bases trying to merge/append to a new base while keeping track of which tables they were in
I have something like the pic below where I have 50-250 companies in multiple yearly lists. Any single company can only be in a list one. But a single company can be in any set of lists and years.
I want to merge the companies in the yearly lists into a unique list of companies where the lists that they each show up on are linked/recorded.
Then, I want a list of the lists that i’ve been tracking. (this is easy to do manually but curious to do it via linked)
What do you think may be the best way to accomplish this? Essentially, if the new yearly list has a company that’s not in my overall base, append it. If it is there, merge (or don’t do anything except for adding it to the selection columns of lists.
I included a rough sketch of what I’m looking for. Right now, I have a bunch of the separate tables on the left and a list of the lists (middle table). Trying to get the right table of companies and share that.
Any help would be appreciated - thanks!
