+ Reply to Thread
Results 1 to 3 of 3

Return value based on most common words between two columns

  1. #1
    Registered User
    Join Date
    06-20-2020
    Location
    India
    MS-Off Ver
    10.02
    Posts
    6

    Return value based on most common words between two columns

    I receive 200 stories per day from which I shortlist X number of stories. I also have a database of 12,000 shortlisted stories. I want to develop an algorithm, which matches contents of column A (which has 200 new stories) and column B (which has all the stories from the database) and generate the closest matched story based on the common words between the two, while ignoring all stop words. I also want it to generate the % of common words between the new story and the common story returned.

    Example:

    Column A has 200 stories, ranging from A1 to A200. Column B has 12,000 ranging from B1 to B 12,000. I want to match A1 with B1 to B12,000 and return the story with which it shares the maximum number of common words.

    I have also attached an excel file with some sample statements for both Column A and Column B.

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Return value based on most common words between two columns

    the below is not something I pulled together for your query but, rather, something I did along time ago for someone else (under a different username) - but it might help?
    (there will be other alternatives, obviously)

    if it doesn't work for you we can leave there as, tbh, it would be time consuming to modify ;-)

    with the UDF stored in a module, you could use this along lines of:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-20-2020
    Location
    India
    MS-Off Ver
    10.02
    Posts
    6

    Re: Return value based on most common words between two columns

    Thanks XLent. I will try using your code and update with the results!

+ 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. Return most common, 2nd common...within the data range
    By tantcu in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2015, 06:06 PM
  2. How to compare text of same row in two different columns and return matching words?
    By guest2013 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-17-2015, 03:22 PM
  3. [SOLVED] Placing dates into multiple columns based on common ID
    By theletterh in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-29-2014, 11:41 AM
  4. [SOLVED] Cross Reference two columns and return common values?
    By chewedbacca in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-27-2013, 12:29 PM
  5. Replies: 1
    Last Post: 05-01-2013, 07:57 PM
  6. Linking 2 excel worksheets based on common columns
    By teewhy in forum Excel General
    Replies: 1
    Last Post: 10-18-2012, 02:12 PM
  7. Merging Columns from 2 sheets based on a common column
    By skilaru in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-23-2012, 02:02 PM

Tags for this Thread

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