remove duplicate AND its original rows

**TAX** · 03-26-2012, 09:37 AM

Hiyah, new here

This seems easy yet i am struggling with it.

i have a spreadsheet well a csv file made for mailshotting.

85,000 rows.

every month i get an update of people to add amend and delete from the database.

My problem is the deleting. So i know whether to add delete or ammend, the column c is called status and so houses a A D or M

If i merge the amendments sheet sometimes 250 extras, to the 85000 , what i am looking to do is use the c column where it sees a D to delete that and the corresponding existing entry on one of the original 85,000 rows. Of course if i run the duplication tool in excel 2010, i just see the 195 doubled entries ( this months deletions ) and if i delete of course only the duplicates go.

I do have to uncheck a few columns for it to successfully find the duplicates.

Perhaps i am looking at it the wrong way. Perhaps i can write some macro that looks at the ammendments sheet and if it isnt in the full sheet add it to the full sheet, and if it is in the full sheet delete it from the full sheet. The rows for deletion, on the original the status code would be different.

I don't how how to delete a row though, or do macro's. I could do some formula i guess but then i would need to add it to each months ammendments. As if would be easier in my mind to check the smaller list against the bigger one rather then vice versa.

Essentially the csv file is a few codes names and addresses for 85,000 people and growing

**Dennis7849** · 03-26-2012, 12:44 PM

What would help here is some dummy samples. Can you provide samples of the primary database(before changes), the amendments and the primary database(after changes). These can be greatly reduced versions. The samples should have enough bogus data to show what the proper operations do.

**TAX** · 03-26-2012, 01:16 PM

ammendments.xls database.xls

of course the actual files are in csv format which the uploader didnt like,

but you get an idea of the structure of the layout

Thankyou

**TAX** · 03-27-2012, 10:16 AM

after_ammendments.xls

sorry i forgot to post the last one, this is the end result with the deletions and the new additions and the ammended entry

**TAX** · 03-29-2012, 02:12 AM

well one thing for sure, at least i wasnt wrong when i said i thought it would be easy, but i couldnt get it around my head. Seems you guys are struggling too.

i think i need somethign like if c2="d" and match($a$2:$a$89000&$b$2:$b$89000 the $address of the row where it found the D in sheet 2 matches the ($a$2:$a$89000&$b$2:$b$89000 in sheet one, then remove the row.