valmatic
asked on
Find where data in column b does not exist anywhere in column a data list
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It may not be the most effective but if you have all the data in the workbook I usually add a column for comparison and put in a formula like "=Countif(A1:A500,B1)" then after I have copied that down for all the rows. Copy and paste special --> values then you can sort by your new column to find the ones that match or do not.
somehow attachment did not make it... here it is:
Book1.xlsx
Book1.xlsx
ASKER
Ken that worked great. It was a little messy since I had delimited data in columnB but I worked it out...
thanks
thanks