Deleting matching rows from different excel sheets

**cnash52** · 03-14-2013, 12:48 PM

Twice a month I have to pull a report to check for additions or deletions to our tracker. I have been doing this manually for months and I have tried to think of a way to have excel do this for me but I cant seem to make anything work. The report has numerous coumns and the number of rows vary every report. An example of the data is:
Name SSN Course Date
John 1234 Blah1 1/13/2013
Jim 2345 Blah2 2/13/2013
James 3456 Blah3 3/13/2013
There are more columns, but what I want to do is find the data in rows X through X that matches data in rows Y through Y and delete the data in row Y that matches row X. That way when I pull the report and add it to the bottom of my current sheet I can run the script and won't have to go line by line to check for changes. I'm sorry if this isn't clear and I can give more information based on what is needed. If easier, I can also pull the new report into a new sheet and then have the script compare cells from one sheet to another. I can't add the actual excel sheet due to security. I know there are excel guru's on here that can make my life easier. Any ideas and what else do you need? Thank you in advance!

**arlu1201** · 03-14-2013, 02:21 PM

There is one way you can do this -

If you 2 or more sheets that need to be compared with each other - we can consolidate all the data from each sheet into one new tab with one column giving the sheet name from where the data came from.
Clear the data in all the sheets except this newly consolidated sheet.
Compare line by line using some unique identifier column(s) and the column containing the sheet name.
Delete the duplicates.
Reload the data from the consolidated sheet into the other sheets based on the column containing the sheet name.
Delete the consolidated sheet.

Would this work for you?

**cnash52** · 03-14-2013, 02:57 PM

Yes, though I have no idea about the logical sequence to perform those tasks. Your breakdown seems very clear and will perform the required tasks with the appropriate product. Thank you for your prompt response and I am hopeful that with your help I will be able to make this tedious task mush simpler. I love excel and I wish I had the knowledge some of the users on this site have.

**arlu1201** · 03-15-2013, 01:11 AM

Please upload a sample file which has the exact format as the original.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**cnash52** · 03-15-2013, 08:01 AM

Report Example.xlsx
Alright here is an example of what the data sheet looks like. The column headings in the table are the same. I have removed all data from the table and changed the sheet name. The hidden columns are information that isn't needed and are always hidden in my reports.

**cnash52** · 03-18-2013, 10:28 AM

Did the attachment work?

**arlu1201** · 03-18-2013, 10:34 AM

Yes it did. But you need to put data in it. I wouldnt be able to test the code without it.

**cnash52** · 03-25-2013, 09:06 AM

Report Example.xlsx
I tried updating the old upload but apparently it didn't upload the correct data.

**cnash52** · 04-16-2013, 02:08 PM

I am starting to lose hope that I will get resolution. I haven't heard back since I uploaded the sheet with data in it.

**arlu1201** · 04-17-2013, 10:51 AM

Sorry for the delay in getting back to you.

You had mentioned that 2 different sheets need to be compared to each other right? But your file in post 8 has only 1 sheet of data.

Also, which are the fields which need to be compared to ascertain if the row is a duplicate?

**cnash52** · 05-06-2013, 09:55 AM

Report Example.xlsx
I have duplicated the first sheet to the second sheet and modified a few of the cells in column P. If the code compared rows B, P, and V and deleted the rows that contained the same information in those three cells from the orginal sheet I believe it would work accurately. If the code could compare those three cells and follow the process you listed in the begining of our discussion it would be amazing. Thank you for your response.

**AB33** · 05-06-2013, 10:19 AM

The attached code deletes the match rows from the original sheet

**cnash52** · 05-06-2013, 02:59 PM

The attached code did in fact work to delete the matching rows. I modified it slightly to fit my Sheet names and it works perfectly. There are some additional tasks I want the code to do which I will try to figure out as I have an idea how to get them to work. Thank you arlu1201 and AB33 for your assistance. It is GREATLY appreciated. I will continue to come back to this site for code questions I have in the future and I am definately adding to both of your reputations.