+ Reply to Thread
Results 1 to 4 of 4

Deleting sentences that has at least 4 words in common

  1. #1
    Forum Contributor
    Join Date
    05-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    145

    Deleting sentences that has at least 4 words in common

    Hello,
    Please see file attached.

    As you can see, its a single column with many sentences (simple text).
    Many of the rows share similar words.
    I need a macro solution (only macro, not formula) that will scan all sentences in column A and delete all rows that has at least 4 words (or more) in common with other sentences, EXCEPT OF THE FIRST ROW THAT HAS THESE WORDS.

    I am not talking about 4 words that has the exact same order, I am talking about 4 words ANYWHERE IN THE SENTENCES.

    For example, if Column A has 6 rows, and a few of them look like these:

    1. when and why lice lay eggs?
    2. when can lice start laying eggs?
    3. when lice lay eggs?
    4. how lice lay eggs?
    5. where can lice eggs live?
    6. when can a lice lay eggs?

    As you can see, rows 1, 3, and 6 has the words "when", "lice", "lay" and "eggs" in common.
    After running the macro, rows 3 and 6 will be deleted and the output will look like this:

    1. when and why lice lay eggs?
    2. when can lice start laying eggs?
    3. how lice lay eggs?
    4. where can lice eggs live?

    P.S: the solution should not be case sensitive, Lice and lice are the same thing.

    Thanks,
    Sami
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Deleting sentences that has at least 4 words in common

    Ive not set it to delete the rows so you can check you're happy with results.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    05-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    145

    Re: Deleting sentences that has at least 4 words in common

    Thank you so much, pjwhitfield!
    It work very well ;-)
    Could you tweak it a bit, so that instead of writing "del" in column B the macro will simply delete the unnecessary row?

    Thanks,
    Sami

    Quote Originally Posted by pjwhitfield View Post
    Ive not set it to delete the rows so you can check you're happy with results.

  4. #4
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Deleting sentences that has at least 4 words in common

    Replace the code with this>
    Please Login or Register  to view this content.
    If someone has helped you then please add to their Reputation

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Counting words in multiple sentences
    By sami770 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-28-2014, 08:34 AM
  2. Vlookup: find words in sentences
    By flx1975 in forum Excel General
    Replies: 1
    Last Post: 05-30-2014, 06:40 AM
  3. [SOLVED] How to manage number of words in sentences?
    By emil9216 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-01-2013, 04:25 AM
  4. List out words from sentences
    By ap_naveen in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-21-2010, 06:58 AM
  5. Split sentences into words
    By sparx in forum Excel General
    Replies: 5
    Last Post: 02-22-2006, 03:51 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1