An easier way

Good morning, 

I've recently started working on a project and was introduced to a process that I think could be made significantly more efficient.  Each month we get a download of records and we have to add these records to a running historical master.  The team currently copies/pastes, and was using VLookup to remove duplicates. We do this for about 8 different files. 

Depending on which file we are talking about, there are currently 35k to 100k records in the historical master, and the monthly file contains 1k-10k new but including some duplicates.   It has come to our attention that the field source being used to deduplicate was changed sometime in the past year so all of the deduplication in the past year is faulty and this has to be rerun using a different field.   

I have several questions- 1) is there not an easier way, VBA, query, something that can automate the monthly appending of the historical master file, 2) what would be the most efficient manner of removing the oldest version of duplicates (historical file has the same customer listed twice but we want to keep the most recent record) found in a file this large now that we have to scrub the ENTIRE thing, 3) I feel like we are going at this whole process incorrectly so if there are any ideas or suggestions for a better way to accomplish this task please share.  We are using Excel for Mac so that is a consideration and I am  completely unfamiliar with this product so any help with this new world is appreciated as well. 

Thanks!