So, I have a pair of excel spreadsheets, one is an old list of responsibilities, one new. They deleted a lot of responsibilities in the new version. However, I have done some work with both of these, and long story short the responsibilities have essentially been combined into one big list. I have new list appended to the bottom of the same spreadsheet, and now I am attempting to delete UNIQUE values, AKA the ones that no longer exist in the new spreadsheet.
So basically, I want to find unique values in Column A, and delete the entire row from which these unique values came. Can anyone help me out?
Make a backup copy of the workbook first.
You can use a helper column to determine which are duplicated in the other sheet.
e.g.
=ISNUMBER(MATCH(A2,SHEET2!A:A,0))
where A2 is the first cell to look at in the sheet you want to the deleting in..
Sheet2!A:A represents column A in other sheet named, Sheet2
A TRUE result means a duplicate is found in column A of Sheet2. FALSE means it is not duplicated in other sheet.
Then you can apply Filter to this table, through Data|Autofilter.
And then filter the formula column for the items you want to delete (either the TRUE or FALSE records).. then select and delete those rows.
Then unfilter and you should see the records you wanted to keep. You can now delete the formula column also.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks