+ Reply to Thread
Results 1 to 12 of 12

Most Frequently Occuring Text

  1. 03-25-2015, 06:04 AM #1
    Shanie
    Shanie is offline
    Registered User
    Join Date
    03-25-2015
    Location
    Essex
    MS-Off Ver
    2010
    Posts
    4

    Most Frequently Occuring Text

    I need some help expanding on this formula.

    I have attached an example spreadsheet of what i have done so far. Using the formula =INDEX(B2:B22,(MODE.SNGL(MATCH(B2:B14,B2:B22,0)))), i have found the most frequently occuring text in column B but i know want to incorporate column A, using this as a criteria. So for example the most frequestly occuring text in column B for 'Ireland'.

    I have tried a couple of things but am completely stuck.

    Thank you
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-25-2015, 06:14 AM #2
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Most Frequently Occuring Text

    Hi.

    =LOOKUP(1,0/FREQUENCY(0,1/(1+COUNTIFS(A2:A22,"Ireland",B2:B22,B2:B22))),B2:B22)

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  3. 03-25-2015, 06:25 AM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Most Frequently Occuring Text

    Here's an alternative for you (array formula - CTRL Shift Enter to set).

    XOR LX's solution is (as ever...) better!!
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: Lá fhéile Pádraig sona dhaoibh
    Reply With QuoteRegister To Reply
  4. 03-25-2015, 07:28 AM #4
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Most Frequently Occuring Text

    Quote Originally Posted by Glenn Kennedy View Post
    XOR LX's solution is (as ever...) better!!
    Just different, my friend. Just different!

    Cheers
    Reply With QuoteRegister To Reply
  5. 03-25-2015, 09:09 AM #5
    Shanie
    Shanie is offline
    Registered User
    Join Date
    03-25-2015
    Location
    Essex
    MS-Off Ver
    2010
    Posts
    4

    Re: Most Frequently Occuring Text

    Thankyou both very much,

    I have used the formula =INDEX(B1:B100,MODE.SNGL(IF(B1:B100<>"",IF(A1:A100="Ireland",MATCH(B1:B100,B1:B100,0)))))

    Unfortunatly i have just realised that i need to extend this further, only over 2 sheets but i am now stuck on this. So for example, one sheet would be for distributor 1 and another sheet would be for distributor 2, how would i be able to find the most frequestly occuring text over the 2 sheets.


    Many Thanks
    Reply With QuoteRegister To Reply
  6. 03-25-2015, 09:36 AM #6
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Most Frequently Occuring Text

    Perhaps you could start by attaching an actual workbook with your expected results clearly outlined?

    Regards
    Reply With QuoteRegister To Reply
  7. 03-25-2015, 10:00 AM #7
    Shanie
    Shanie is offline
    Registered User
    Join Date
    03-25-2015
    Location
    Essex
    MS-Off Ver
    2010
    Posts
    4

    Re: Most Frequently Occuring Text

    Have attached,

    The results tab, highlighted in yellow, this are the results i am trying to find.

    thank you.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 03-25-2015, 11:00 AM #8
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Most Frequently Occuring Text

    This is a complex solution, so I thought I would first check if you were sure that this was not something you could do semi-manually by incorporating the data from the two sheets into a third sheet and then applying the formulas given to you on that sheet.

    If not, then I'll happily present you with a solution which will work for your current set-up, though, like I said, it's not the most straightforward of constructions.

    Regards
    Reply With QuoteRegister To Reply
  9. 03-25-2015, 11:26 AM #9
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Most Frequently Occuring Text

    I can do it across two sheets OK (thanks to Ashish Mathur..), but I'm struggling a bit with Ireland and Scotland separately... In fact I'm a bit lost!!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 03-25-2015, 11:38 AM #10
    Shanie
    Shanie is offline
    Registered User
    Join Date
    03-25-2015
    Location
    Essex
    MS-Off Ver
    2010
    Posts
    4

    Re: Most Frequently Occuring Text

    Thank you, I will see if the information can be put onto one sheet first.

    Thank you very much.
    Reply With QuoteRegister To Reply
  11. 03-25-2015, 11:49 AM #11
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Most Frequently Occuring Text

    Quote Originally Posted by Glenn Kennedy View Post
    I can do it across two sheets OK (thanks to Ashish Mathur..), but I'm struggling a bit with Ireland and Scotland separately... In fact I'm a bit lost!!
    Glenn - check your formula using Evaluate Formula. FREQUENCY only works with numbers, not text!

    Regards
    Reply With QuoteRegister To Reply
  12. 03-25-2015, 12:08 PM #12
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Most Frequently Occuring Text

    XOR LX... I guess that's why you're a Guru. Thanks.

    Shanie - ignore my post at #9.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. most frequently occuring set
    By apok9f in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-25-2013, 04:29 AM
  2. Macro or formula to find the 2 most frequently occuring numbers[SOLVED]
    By flexalong in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-14-2011, 04:34 PM
  3. Most frequently occurring text
    By Deanomcbeano in forum Excel General
    Replies: 4
    Last Post: 08-12-2009, 08:58 AM
  4. [SOLVED] Extracting the most frequently occuring text from a range
    By Phil in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-09-2006, 08:45 AM
  5. Extracting the most frequently occuring text from a range
    By Phil in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-08-2006, 10:51 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1