+ Reply to Thread
Results 1 to 4 of 4

Find the most frequent and second most frequent text string whilst ignoring numbers

  1. #1
    Registered User
    Join Date
    03-19-2012
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    21

    Find the most frequent and second most frequent text string whilst ignoring numbers

    Hi All,

    I’m trying to identify the most frequent occurring text string in a row followed by the second most frequent text string whilst ignoring any cells in the row that contain numerical values.

    As an example, I have a table of sales results (attached) by week by region. Each week, the top salesperson and number of sales are updated into the applicable row for their region.

    Sales.xlsx

    Through the forum pages I have been able to identify the formula for determining the most frequent text string in a row using an array formula –

    Please Login or Register  to view this content.
    However,
    1) I am unsure how to find the second most frequently occurring text string in a row, and
    2) Depending on the frequency of sales each week, it is possible the array formula will return the most frequently occurring numerical value (sales volume), however, I would like to ignore the numerical value and only return the text string value (for both the most frequent and second most frequent values)

    I understand that if there is a tie the array formula will provide the first most frequent occurring text string, spread sheet attached is for example only.

    Thanks for your help
    First time poster so please let me know if you require any additional information.

    Thanks,
    Last edited by Puni; 03-20-2012 at 09:56 PM. Reason: Solved

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Find the most frequent and second most frequent text string whilst ignoring numbers

    You could use MODE.MULT() in 2007 and above, unfortunately I only have 2003 available at the moment, so I can't test an example for you.
    Try googling "mode.mult excel function" to find examples
    MODE.MULT function is the official MS site.

    Note on your current workbook, you might be safer using these array formulae.
    In C6
    Please Login or Register  to view this content.
    and in C7
    Please Login or Register  to view this content.
    Last edited by Marcol; 03-20-2012 at 06:49 AM. Reason: Added link to MS help
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Find the most frequent and second most frequent text string whilst ignoring numbers

    Hmm?
    I can't see a clear solution using MODE(), and I'm not entirely sure it is the best approach.

    In addition to the previous post ...
    This array in D7
    Please Login or Register  to view this content.
    and this array in D8
    Please Login or Register  to view this content.
    However MODE() will not recognise unique cell values, i.e. the values will have to appear more than once to be recognised.
    If there is no duplication returned MODE() will return #N/A.

    This isn't perhaps ideal, but might well serve your purpose.

    To better understand the returned result, change the value in row 7 (yellow cells) to either 11, or 15.
    See this workbook
    Attached Files Attached Files
    Last edited by Marcol; 03-20-2012 at 07:48 AM.

  4. #4
    Registered User
    Join Date
    03-19-2012
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Find the most frequent and second most frequent text string whilst ignoring numbers

    Hi Marcol,

    Thank you so much for your informative and quick response, much appreciated, has helped immensely. Where a duplicate does occur I can use IFERROR and conditional formatting to return an empty cell where the formula returns #N/A.

    Thanks again,
    Puni

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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