+ Reply to Thread
Results 1 to 6 of 6

Return words in descending order of frequency from a column containing multiple sentences

  1. #1
    Registered User
    Join Date
    07-17-2013
    Location
    miami, fl
    MS-Off Ver
    Excel 2016
    Posts
    28

    Return words in descending order of frequency from a column containing multiple sentences

    I have a column that contains a restaurant review on each cell, each review has multiple words. I'd like to be able to return all the individual words that appeared in all the reviews in descending order of frequency. It would be good to be able to ignore commas and periods as well.


    So for example, let's say column A had the following values for A1,A2,A3:

    Great Service, very satisfied.
    I was very disappointed with my meal, but I loved the service.
    I'll come back again for the service alone.

    The program should return:

    Service 3
    I 2
    very 2
    the 2
    Great 1
    I'll 1
    .
    .
    .

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Return words in descending order of frequency from a column containing multiple senten

    You forgot back, again and some more.

    First I would copy every row to another column and transpose it, next row the same but transpose it below the previous one etc.
    This to separate all the words, of course this will include punctuation marks

    The next step is use advanced filter on this new column to copy the unique records to another column

    and next to this new list place a formula using the function COUNTIF for each value to count the occurrence in the row with all the words.


    The best thing would be a macro, so ...

    Start the macro recorder and follow all the steps I just mentioned.... and the macro is there, all you need to do is edit to make it dynamic in case there are more rows.

    Hope it helps to point you in the right way, it's just an idea that popped-up and I know you can achieve it this way, of course there may be more simplere ideas but ... I don't have them
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Return words in descending order of frequency from a column containing multiple senten

    The transpose wont work, you'll have to set text to columns first and then copy and transpose.
    I'll try and put it together for you

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Return words in descending order of frequency from a column containing multiple senten

    A working macro, just paste all the sentences you want in Column A and press the blue arrow button
    If you press it twice the list will be processed again appending the words, it does NOT clear the previous count
    Since it's past midnight ... i'm off and away
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    11-05-2019
    Location
    Tiki Island, TX
    MS-Off Ver
    Office 365
    Posts
    168

    Re: Return words in descending order of frequency from a column containing multiple senten

    It looks like I finished my work after somebody already took an approach. Still you may want to use the function I wrote. It is shown below and I have attached the spreadsheet where I show how it works. Like any other function just type =WordsWithCounts(put your cell range to test here) and it should work. One trick though, if you want the linefeeds to work so each word shows on a different line you have to format the cell you are putting the formula in to Wrap Text before it calculates the formula.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by DavidBowman; 11-09-2019 at 01:42 AM. Reason: Add attachment

  6. #6
    Registered User
    Join Date
    07-17-2013
    Location
    miami, fl
    MS-Off Ver
    Excel 2016
    Posts
    28

    Re: Return words in descending order of frequency from a column containing multiple senten

    Works perfectly, thank you!

+ 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] Finding and arranging rows then displaying their frequency in descending order:
    By LAVA2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-19-2018, 05:44 AM
  2. [SOLVED] Counting words in multiple sentences
    By sami770 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-28-2014, 08:34 AM
  3. [SOLVED] Column of single words as filter for columns with sentences
    By [email protected] in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 12
    Last Post: 01-23-2014, 12:11 PM
  4. Replies: 1
    Last Post: 01-20-2014, 04:03 PM
  5. Replies: 6
    Last Post: 01-17-2014, 10:41 AM
  6. Remove Stop Words from a column containing 16000 rows of sentences
    By Abhayrajify in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-30-2013, 07:14 PM
  7. [SOLVED] Date in descending order to come up automatically in a column?
    By Snugglebums in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-11-2005, 12:06 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