+ Reply to Thread
Results 1 to 21 of 21

List most frequently occurring strings (n most repetitive words) in a sheet range

  1. #1
    Registered User
    Join Date
    06-30-2020
    Location
    Barcelona
    MS-Off Ver
    2019
    Posts
    17

    List most frequently occurring strings (n most repetitive words) in a sheet range

    Hi everyone,

    There is a sheet that contains cells with strings (letters and numbers). In this sheet, we could have also Tables and PivotTables with those strings.
    These words are not necessarily in a column or row and could be anywhere in the sheet.
    I want to find most n frequent strings which occurred in this sheet in a table with their repetition numbers if the result table is:
    (1) in the same sheet
    (2) in a different sheet

    A sample excel file has been attached.
    Thanks in advance
    Attached Files Attached Files
    Last edited by sa13er; 07-06-2020 at 07:57 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: List most frequently occurring strings (n most repetitive words)

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    06-30-2020
    Location
    Barcelona
    MS-Off Ver
    2019
    Posts
    17

    Re: List most frequently occurring strings (n most repetitive words)

    Thank for your help. I have attached a sample file in the first post.

  4. #4
    Registered User
    Join Date
    06-30-2020
    Location
    Barcelona
    MS-Off Ver
    2019
    Posts
    17

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    I would appreciate it if anyone could help with this issue. It's very urgent.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    I'm afraid we don't do urgent. What we offer her comes entirely for free and is discretionary - you will need to be patient. I will have a look at your workbook now.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    OK - I have had a look. I am not surprised that 72 views of this thread have led to no offers of help. I have no idea what you are trying to do or why - sorry. I won't be able to help you with this.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    Don't open duplicate threads - if you want this thread moving to the VBA section, just ask.

    I think now might be a good time for you to read our rules: https://www.excelforum.com/forum-rul...rum-rules.html

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    Please try at ResultSheet

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


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


    confirm both with Ctrl+Shift+Enter
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    06-30-2020
    Location
    Barcelona
    MS-Off Ver
    2019
    Posts
    17

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    My apology. You are right. I was dissapointed at that time and I thought there was no way to use formula and functions and then I opened a topic in VBA forum.

  10. #10
    Registered User
    Join Date
    06-30-2020
    Location
    Barcelona
    MS-Off Ver
    2019
    Posts
    17

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    Quote Originally Posted by Bo_Ry View Post
    Please try at ResultSheet

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


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


    confirm both with Ctrl+Shift+Enter
    Thank you for your response. That was great. But when I transferred these formulas to original excel, nothing is shown in the table (due to error because of PivotTable I think).
    For clearing the issue, I attached a sample file with two table results. One of them with the range without PivotTables and other including pivotTables.
    I think it is because of PivotTables filter.
    Thank you for your help
    Attached Files Attached Files
    Last edited by sa13er; 07-07-2020 at 09:05 AM.

  11. #11
    Registered User
    Join Date
    06-30-2020
    Location
    Barcelona
    MS-Off Ver
    2019
    Posts
    17

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    Is there any solution for the previous post problem?

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    Please be patient.

  13. #13
    Registered User
    Join Date
    06-30-2020
    Location
    Barcelona
    MS-Off Ver
    2019
    Posts
    17

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    Quote Originally Posted by AliGW View Post
    Please be patient.
    Sure. What do I do if I want to know what is the VBA code for a similar problem?

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    Either update your title to say formula or VBA, or i can move the thread to the VBA section. Which do you want to do?

  15. #15
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    Open Power Query/Get and Transform. Click on New Query.
    Open blank query in the editor, launch Advanced Editor and paste in the following code.
    Change File paht in blue

    Please Login or Register  to view this content.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    06-30-2020
    Location
    Barcelona
    MS-Off Ver
    2019
    Posts
    17

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    I have done it as you said and also changed it a little bit for dynamic file paths & name as follows:
    In one of the cells, I put this code and changed the cell's name to "FolderPath":
    Please Login or Register  to view this content.
    Then I changed your power query code to this code:

    Please Login or Register  to view this content.
    Thank you for solution. Here I have two questions and I would be appreciated for your time and help.
    1- How to change this power query code in a way that it just get cells with non-numeric contents.
    2- How to change this power query code in a way that it just get contents of ranges in a sheet that we specify (for example Ranges like E8:Q30 in the TargetSheet or just three columns like Q:Q,I:I,B:B in the TargetSheet).

  17. #17
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    Please try
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    06-30-2020
    Location
    Barcelona
    MS-Off Ver
    2019
    Posts
    17

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    Thank you. That worked, but when I tried to test the final solution I encountered a problem.
    When I change the filter settings of PivotTables in "TargetSheet" or remove the filter of them and then refresh the Table in "ResultSheet", it seems the result data doesn't change.
    I attached a sample file of final changes. Thank you for your help in advance.
    Attached Files Attached Files

  19. #19
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    You need to save file and refresh the query.

  20. #20
    Registered User
    Join Date
    06-30-2020
    Location
    Barcelona
    MS-Off Ver
    2019
    Posts
    17

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    You are right. I forgot to save the file.

    I think this is the last question.

    How we can show n different data with the most repetition (This is the main point in my first post of this thread, not specifying the least number of repetitions)?
    For example, if we have a data set like this:
    A,A,A,A,A,B,B,B,B,C,C,C,C,D,D,D,E,E,E,F,F,F,G,G,G,H,H,I,I,J
    and if we enter 4 in the cell, four different data with the most repetition are A (5 times), B (four times), C (four times), and D, E, F, and G (three times) so, the result would be:
    A|5
    B|4
    C|4
    D|3
    E|3
    F|3
    G|3

    if we enter 3 in the cell, three different data with the most repetition are A (5times), B (four times), and C (four times), so the result would be:
    A|5
    B|4
    C|4

    if we enter 2 in the cell, two different data with the most repetition are A (5times), B and C (four times), so the result would be:
    A|5
    B|4
    C|4

    Thank you in advance.

  21. #21
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: List most frequently occurring strings (n most repetitive words) in a sheet range

    Try

    Please Login or Register  to view this content.

+ 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. Replies: 5
    Last Post: 04-28-2020, 08:17 AM
  2. Most frequently occurring text
    By okat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-22-2017, 09:41 AM
  3. Finding Most Frequently Occurring Combinations
    By seanpalmgren in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-18-2015, 04:01 PM
  4. Return most frequently occurring text in row
    By penfold in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-13-2011, 12:38 PM
  5. Count and identify most frequently occurring words
    By SueWithQuestion in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-24-2011, 01:25 PM
  6. Most frequently occurring text
    By Deanomcbeano in forum Excel General
    Replies: 4
    Last Post: 08-12-2009, 08:58 AM
  7. most frequently occurring value
    By Pivotrend in forum Excel General
    Replies: 3
    Last Post: 12-23-2005, 08:10 AM

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