Need rows in Column A removed if...

  • Thread starter Thread starter registrations
  • Start date Start date
R

registrations

Hi There,

I have rows of text strings in Column A and in Column B.

I need rows in Column A removed if they partially or exactly
match with text strings in any Column B row.

Your input is appreciated!
Alexa
 
Assuming your data in A2:B100, in C2 put the following and copy down to C100

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(A2,$B$2:$B$100))),"DEL ME","")

Filter on Col C and choose "DEL ME".

Select the data in Col C and then do edit / Go To / Special / Visible cells
only

Then do Edit / Delete / Entire row

Take the filter off of Col C and you are done.

CAUTION:-

This assumes that the entire row can be deleted if it meets your citeria,
but your text is open to interpretation. What I have given you will delete
the entire row which means that the data in Col B for those rows will also
go.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------­------------------------------­----------------
 
If yo would be so kind...........please elaborate on exactly what "partially
or exactly match" means in this situation.......give us an example of what
would be considered "partial" match.......and do you want the entire row
deleted, or just one cell in column A.........

Vaya con Dios,
Chuck, CABGx3
 
Hi there,

By "partially match" I meant if a part of one text string matches part
of other text string. I worded it incorrectly anyway, so sorry about
that.

I tried to use the provided formula to do the task but I got an error
message all the time. I'm not a very advanced Excel user.

I'd be very happy if someone would create a simple macro that deletes
cells from Column A if they match with any cell in column B.

Regards,
Alexa
 
Back
Top