cleaning up my list - remove items in the list

**tmssj2000** · 04-04-2009, 12:40 PM

Hi to all

Appreciate your help in this.

Let's say I have a list of 500 keyword phrases as below:

CellA1 guitar chords
CellA2 guitar chord
CellA3 guitar chords with scores
CellA4 guitar chords expert
.....

"guitar chords" actually appears in CellA3 and A4. So I will like to remove CellA1.

"guitar chord" (without 's') do not appear anywhere else. So I will like to retain CellA2.

The process is repeated for each cell until the end of list. How should I do that via formula and macro?

**Leith Ross** · 04-04-2009, 01:56 PM

Hello tmssj2000,

Welcome to the Forum!

Are you looking for just these 2 phrase pairs on your worksheet or will there be more?

**tmssj2000** · 04-05-2009, 09:27 AM

Maybe I should rephrase my question and my requirements.

Let's say I have a list of 5 keyword phrases as below:

CellA1 guitar chords
CellA2 guitar chord
CellA3 guitar chords with scores
CellA4 guitar chords expert
CellA5 guitar chords with scores of music

The macro will begin with CellA1 and check the rest of the list (CellA2 till end of list) for any cell that contains the contents of CellA1. Once it is able to find an instance for that, it returns "YES" in CellB1. In this case, the first instance is found in CellA3 and so CellB1 returns "YES".

Next, the macro moves to CellA2 and check the rest of the list (CellA3 till end of list) for any cell that contains the contents of CellA2. CellA2 contains "guitar chord" so the macro will check CellA3 till CellA5 on whether they contain "guitar chord". In this case, CellB2 will return "NO" as there is no cell that contains "guitar chord" exactly. That means "guitar chords with scores" is a "NO" while for example "guitar chord with scores" is a "YES".

The macro then moves on to subsequent cells down ColumnA to keep on checking until the end of list.

This is just a make-up list of 5 phrases. What I require is a general macro that can handle at least 5000 phrases.

**Leith Ross** · 04-05-2009, 07:00 PM

Hello tmssj2000,

I have a few ideas for a solution to this. Rather than spend time posting questions and waiting for answers, it would be faster if you post a sample workbook.

**Leith Ross** · 04-05-2009, 11:54 PM

Hello tmssj2000,

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Corss posted at http://www.mrexcel.com/forum/showthread.php?t=382428