Multiple List Clear

[kod2th3e]

I have a list on sheet3 that is generated by a user and a list on sheet2 that is automatically provided. What I would like to have happen is when when a macro is run on sheet3 it goes down the list (could be variable in length) item by item and clears any identical values in sheet2. For example:

Sheet2:
Col1
123a
123b
123c
123d
123e
123f

Sheet3:
Col1
123a
123c
123f

So, when the macro is run it'll find 123a, 123c, & 123f on sheet2 and clear them but nothing else.

Thanks in advance for looking/providing assistance,
-Cody

 

[jbeaucaire]

1) On Sheet2, if there is not a row of titles in row1, insert one
2) Put this formula in B1 and copy down:

=ISNUMBER(MATCH(A1, Sheet3!A:A, 0))

3) You now have a list TRUE/FALSE for all the matches.

4) Turn on the Data > Filter > Autofilter

5) Filter column B for "TRUE"

6) Delete all visible rows

7) Turn off the autofilter and clear column B.

 

[kod2th3e]

I was hoping to accomplish this with a looping macro. I have headings at the top of each column, I was just looking for something to go to the first item on sheet3 find and delete it on sheet2 then go to the next item on sheet3 and find and delete it on sheet2 and so on. Thoughts?

Thanks for the response,
Cody

 

[jbeaucaire]

The technique shown is the correct method to use, even if you turn on the macro recorder and let it record you doing all that. Then a few small tweaks will give you a reausable macro.

Never loop line by line when you can evaluate the entire data range all at once, delete all at once.

Try it... turn on the macro recorder, then follow the steps above manually. When done, paste your resulting code here and we can tweak that into something reusable.

 

[kod2th3e]

I changed the formula a little bit to match my needs a little better:

=IFERROR(IF(MATCH(B3,'Sheet 3'!$A:$A,0),"Y","N"),"N")

The isnumber wasn't working all the time because of the varying length in the values (wasn't always a number or not always text). I appreciate the help it has greatly reduced some frustration.

Thanks,
-Cody