Forum Discussion
Need Help Updating a List
Of course it's possible to do what you're asking. I'm pretty sure you'll get some suggestions about how Power Query can do it. I work on a Mac and Power Query isn't available yet on Macs, so I will have to point you in a different direction.
Without actually seeing how your data are arrayed, it's hard to give really specific guidance. And you don't say how experienced you are with Excel. So what I give you will presume you are familiar enough, or confident enough, to do some experimenting.
Last year in the newest updates to Excel some functions were introduced that should enable you to accomplish what you're trying to do. Here's a YouTube video that explains them, with some good demonstrations. I have in mind in particular the UNIQUE and FILTER functions. Used properly, you should be readily able to accomplish the tasks you've described.
If you need further help, may I suggest that you post some samples (rendered anonymous) of the actual spreadsheets you have. Change names and other identifiable characteristics.
Here's that video link: https://www.youtube.com/watch?v=9I9DtFOVPIg