Compare multiple columns to determine matches
Posted on 2014-03-07
I have been given an audit that I do every month that requires comparing different sources to determine which items from each source are not in the others. Essentially, I am given a directory dump with files in it, and told to validate that each of the files in the directory is also present in a spreadsheet. The spreadsheet has 2 different columns that I have to validate the directory list against, but it only has to exist in one of the columns in order to pass the audit. So, what I have been doing is scraping the .txt document that contains the files, and copying that into a column in Excel. Then, I take the 2 columns out of the other Excel spreadsheet and compare them one at a time against the new sheet that I created. It ends up working, but it's really not repeatable, or consistent, and it takes forever. I'm looking for a more elegant solution. It doesn't matter to me if it's in Excel, Access, VBA, SQL, or whatever. I just need some way to do it better than I've been doing.