Delete cell and adjacent column if the same data is found is found in another column

I have a macro that creates a pivot table and then copies the output over to a new worksheet. Problem is the row data that I copied over has additional unwanted entries. Can a macro or VBA look at the new column of data and see if it matches a list of data in another column. Then if it does, remove the the data in column 1 with it's adjacent cell and then shift everything up? I attached a sample of something that looks like what I have.example1.xlsx

Hi Nguerra,

The code below creates a reference column (C) to mark whether the data item appears in the pre-existing list, and deletes these. It then clears the contents of the reference column.

Limitations = I have sorted the data by the reference column, so this may need to be resorted following this process to an order that suits you.

I hope it helps!

KimberleyBob

Please Login or Register  to view this content.

Thank you KimberleyBob, Your macro works great on the file I posted but I'm having trouble adding it to my larger file. by adding comments, I can see where you added the C column with 0s' and 1's. After this step is completed however I'm running into issues. It looks like the delete and shift up is modifying my existing data throughout the worksheet. Is it possible to just look and see if the value in the C column is either a 0 or a 1 and if a 1, only delete cells Ax, Bx, Cx, and then shift up columns A,B, and C without affecting the rest of the worksheet?

It only currently deletes the cells in columns A&B - however it may be the sort function which is causing some problems.

Try this one instead - I've asked the data to sort only columns A:C, rather than the whole sheet (based on C):

Please Login or Register  to view this content.

I also added some comments so that you can see what each step is doing.