+ Reply to Thread
Results 1 to 17 of 17

Most common string

  1. #1
    Registered User
    Join Date
    03-03-2015
    Location
    Denmark
    MS-Off Ver
    2011
    Posts
    12

    Most common string

    Hello all Experts

    I have two coloumns one with a lot of names and another one with some breakfast consumptions.
    It could look like this
    Coloum 1: Column 2:
    Thor Banana
    Anna Frosties
    Martin Bun
    Peter Banana
    Martin Bun
    Thor Bun
    Anna Frosties
    Peter Banana
    Anna Bun
    Martin Banana
    Thor Bun
    Peter: Bun

    I would like to make a function that for each name calculates the most common string for that name and returns it.

    But I have no idea how to start

    Thank you for your help!

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Most common string

    Will you pls provide sample file with expected result
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    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
    43,893

    Re: Most common string

    Hi there. Try this out. It's an array formula which must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    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

  4. #4
    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
    43,893

    Re: Most common string

    Just in case it is possible to have tied results - this array formula will list both/all. See purple shaded cells.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-03-2015
    Location
    Denmark
    MS-Off Ver
    2011
    Posts
    12

    Re: Most common string

    Thank you, Glenn. For some reason I cannot download you latest attached file..

  6. #6
    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
    43,893

    Re: Most common string

    Works OK for me. I've saved it again with a different filename...
    Attached Files Attached Files

  7. #7
    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
    43,893

    Re: Most common string

    If that doesn't work... drop me a PM with your email adddress & I'll send it that way.

  8. #8
    Registered User
    Join Date
    03-03-2015
    Location
    Denmark
    MS-Off Ver
    2011
    Posts
    12

    Re: Most common string

    Thank you glenn. What if i have a third column with dates and I want it to be in a period fx 2013. How do I get that into my function.
    Something like
    'Data Sertica'!$G:$G;">="&DATE($C$3;1;1);'Data Sertica'!$G:$G;"<"&DATE($C$4;1;1)
    Where 'Data Sertica'!$G:$G is the date, and C3 is 2013 and C4 2014?

    Thank you

  9. #9
    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
    43,893

    Re: Most common string

    I'm away for a while. I'll reply in a couple of hours...

  10. #10
    Registered User
    Join Date
    03-03-2015
    Location
    Denmark
    MS-Off Ver
    2011
    Posts
    12

    Re: Most common string

    Thank you, looking forward for your help!

  11. #11
    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
    43,893

    Re: Most common string

    OK. It took me a while to get this working. I've no idea why.. but it seems OK now.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    03-03-2015
    Location
    Denmark
    MS-Off Ver
    2011
    Posts
    12

    Re: Most common string

    Thank you alot!

  13. #13
    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
    43,893

    Re: Most common string

    I think you should edit your last post and remove your email address. I've just sent the file to you...

  14. #14
    Registered User
    Join Date
    03-03-2015
    Location
    Denmark
    MS-Off Ver
    2011
    Posts
    12

    Re: Most common string

    I don't need that counter if there is a tie, because I have a lot of data and therefore there will never be. How can I write the function without the counter, so it is just like your first function you send to me + if(date...

  15. #15
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Most common string

    why you are not attaching sample excel file with your desired / expected result

  16. #16
    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
    43,893

    Re: Most common string

    Here you are.. I'll send by email again - just in case you're still having the same problem with downloading from the Forum.
    Attached Files Attached Files

  17. #17
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Most common string

    I reckon a pivot table is much easier, none of those big long formulas that make my head hurt

+ 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. Merging Cells with common string Values
    By HB07 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-27-2015, 06:52 AM
  2. [SOLVED] Most common string in column with an exception
    By Bytor47 in forum Excel General
    Replies: 5
    Last Post: 04-11-2013, 07:46 AM
  3. How to find common string column from two columns??
    By sanil.henry1982 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-14-2012, 08:34 AM
  4. Convert similar strings into a common string
    By booo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-03-2010, 03:54 PM
  5. Find the lest common string-value in a range
    By ElmerS in forum Excel General
    Replies: 4
    Last Post: 02-01-2009, 06:25 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