If in Column A, Delete in Column B

[mmmarty]

Hi,

I have 30000 cells of data occupying Row B. This data is updated quarterly and 100 cells of data occupying Row A. Updated Weekly.

Currently we autofilter by row A (100 times - Imagine.) and delete all instances of filtered data found in Column B by filtering.

There must be a better way. Any suggestions please.

 

[ADAMC]

Hi i use this for something similar might be a better way but it works....

=IF(ISERROR(MATCH(A1,$B$1:$B$100,0)),"",A1)

Put this in C1 and fill down (Change the cells if needed)
This says look at cell 1 and put it in this cell if it appears in B1 TO B100 otherwise leave blank....
So...If A1 says MMMARTY and column B doesnt would be blank.
Then you know which words to delete from B

Hope this is useful.

 

[mmmarty]

Thanks for that Adam,
It's not quite working the way I hoped though.
As long as I have data in both the A and B columns side by side then I get the blank cell in the corresponding cell in the C column as you suggested.

Ideally, I'd like my short list in the A column to find every instance of itself in the hellava long list in the B column and flag it somehow either in the C column or the B. So that I can delete everything that's not in the A column.

Marty

 

[ADAMC]

If there arent any duplicates my first formual should be fine?
=IF(ISERROR(MATCH(A1,$B$1:$B$100,0)),"",A1)

Ideally, I'd like my short list in the A column to find every instance of itself in the hellava long list in the B column and flag it somehow either in the C column or the B. So that I can delete everything that's not in the A column.
Marty

Thats what the formula basically does, if it isnt in column B the formula cell will be blank and so the cell in column A its checking isnt in B and so can be deleted......?

Another approach could be this:
=COUNTIF($B$1:$B$100,A1)

Then fill down from C1 to the length of values in column A..slightly different approach here it will count how many times A1 appears in B1-B100 if the value is 0 it means A is not in column B and can be removed?

Or something like:
=IF(COUNTIF($B$1:$B$100,A1),"Found","") This will return found if A1 is in B1 TO B100 otherwise blank.....

Or..you could go the opposite way round with:
=IF(COUNTIF(A:A,B1),"True","") This says if B1 is in any of column A show true......

I hope one of these help!