How do I delete the data on one excel sheet from the other?

Question

I have to seperate excel worksheets with data. Sheet 1 has about 60,000 rows and sheet 2 has about 30,000. I want to make sure the data in sheet 2 does not exist in sheet 1. In other words I want to delete the data in sheet 2 from sheet 1. Please help :) Thanks!!!

Answer 1

You need to:

1) identify which Sheet 1 rows appear on Sheet 2,

2) select those rows on Sheet 1

3) delete them from Sheet 1



First, make sure you save, or make a copy of your file in case anything goes wrong.



1) identify the rows which appear on Sheet 2

- hopefully there is a unique field (name, id#, whatever) that is on Sheet 1, and that you can search for on Sheet 2; if there is no single identifier, let us know, because that will get more complicated

- use a VLOOKUP function to see if each identifier on Sheet 1 also appears on Sheet 2:

=VLOOKUP(A2, 'Sheet 2'!$A$2:$A$30000, 1, FALSE)

- this searches for what's in A2 of Sheet1, in column A of Sheet 2

- copy this down to all 60,000 rows in Sheet 1

- have a header for this column, something like "Lookup"



2) If the VLOOKUP doesn't find an identifier on Sheet 2, it will show "#N/A". If it does find it, it will show that identifier. So you want to delete the rows that show anything except #N/A:

- click on this new column with the VLOOKUPs

- Data - Filter - Autofilter

- when you get the little arrow, use it to pick (Custom...)

- in the left box, put Does Not Equal

- in the right box, put #N/A

- this will show all the rows that were found on Sheet 2, and will hide all the rows with #N/A's (which weren't found)



3) Delete the visible rows:

- Select all the rows that are visible

- Edit - Delete (only the visible rows will be deleted)



If you need more info, add detail or e-mail.

.

Answer 2

It sounds like you need to remove duplicate records, yes? I had to do this before. There may be an easier way, but I couldn't come up with one. Here's what I did...



I created a third sheet, then pasted the data from the other two sheets to it. (Be sure the columns match up.) In my case, one of the columns was "ID Number", and I only needed one instance of each ID number. I sorted the combined sheet by ID number, then next to the ID number column, I inserted two blank columns. So let's say ID number was column A. The blank columns inserted are B and C. In column B, row 3 (row 1 was column headers), I used this formula:



IF(A3=A2,"X","")



So if the ID number in row 3 is the same as the one in row 2, it will put an "X" in B3. If not, it will leave B3 blank.



Next, I dragged the formula down to fill in all rows. This marked all the duplicate records with an "X".



Since this is a relative formula, sorting by column B will affect the result of the formla. So I copied column B and pasted it to column C using the "paste special" function under the Edit menu, choosing to paste values only.



Then when you sort by column C, all the duplicates are grouped and can be easily deleted.



Delete the two columns you created and you have one sheet with all your data combined and no duplicates.

Answer 3

I am sure that what you have if you have the data in sheet 2 showing ion sheet 1 is a reference (absolute Cell reference) to data or cells in your sheet 1.



So click on the cells you don't want the reference to show and delete the formular that you see there. That should solve your problem.



However, if it is teh fact that you just want to delete sheet 2 from the whole workbook, just right-click on the sheet stub (Tab) and select delete sheet, that should do that job for you.



Good luck

Answer 4

You must use Macro tool for this problem, in other word you must write a simple script in VB to do it for you.