Find where data in column b does not exist anywhere in column a data list
Posted on 2014-09-17
Hi. I am importing a large amount of data from excel import lists into a 3rd party database. User populated the lists manually but I'm finding inconsistencies in the data and I'm looking for a quick way to compare and correct these inconstencies. User was tasked with creating data lists which in turn are used to populate a number of import spreadsheets. For example: DATA LIST A has maybe 80 records consisting of department names. The import spreadsheet has a number of columns consisting of employee, ID, department, etc.. Import spreadsheet data must exist on one of these data lists. I need to find where a column from the import spreadsheet does not match to an entry in the corresponding data list so I can change it to duplicate a value that exists on the data list. I was going to import all into access and compare there but thought there must be a way to do this in excel.
Instead of trying to find a method to compare across sheets, I can pull both columns of data into a single spreadsheet and just compare columns to make this easier. Is there a fairly simple way to execute this? thanks