+ Reply to Thread
Results 1 to 5 of 5

Formula that writes most repetitive text from selected cells

  1. #1
    Registered User
    Join Date
    05-08-2018
    Location
    Croatia
    MS-Off Ver
    Microsoft Office 2007 Enterprise
    Posts
    12

    Formula that writes most repetitive text from selected cells

    Hi,
    I have excel table that looks like this:

    text1
    text2
    text3
    text2
    text2
    text2
    text1
    text3
    text2
    text2
    text2
    text2

    The most repetitive text in this table is "text2". Now I want formula to automatically find most repetitive text and write it.
    It would be best if it can be done within one cell.
    Regards,
    DarkLord76865

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    5,981

    Re: Formula that writes most repetitive text from selected cells

    Hello and welcome to the forum. Try this:

    =INDEX(A1:A12,MODE(MATCH(A1:A12,A1:A12,0)))

    Adjust the ranges to suit your data.

  3. #3
    Registered User
    Join Date
    05-08-2018
    Location
    Croatia
    MS-Off Ver
    Microsoft Office 2007 Enterprise
    Posts
    12

    Re: Formula that writes most repetitive text from selected cells

    Quote Originally Posted by 63falcondude View Post
    Hello and welcome to the forum. Try this:

    =INDEX(A1:A12,MODE(MATCH(A1:A12,A1:A12,0)))

    Adjust the ranges to suit your data.
    Thanks for trying to help but unfortunately this just returns value not available error.
    I adjusted ranges to suit my data but error still persists. I also checked that data is valid.
    Anyone who knows something about this please post here.

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,489

    Re: Formula that writes most repetitive text from selected cells

    Or try this:

    =INDEX(A1:A12,MATCH(MAX(COUNTIF(A1:A12,A1:A12)),COUNTIF(A1:A12,A1:A12),0))

    Enter with Ctrl+Shift+Enter.

  5. #5
    Registered User
    Join Date
    05-08-2018
    Location
    Croatia
    MS-Off Ver
    Microsoft Office 2007 Enterprise
    Posts
    12

    Re: Formula that writes most repetitive text from selected cells

    Quote Originally Posted by Phuocam View Post
    Or try this:

    =INDEX(A1:A12,MATCH(MAX(COUNTIF(A1:A12,A1:A12)),COUNTIF(A1:A12,A1:A12),0))

    Enter with Ctrl+Shift+Enter.
    Thank you on your help. This is working perfectly.
    If I will have any other questions I will post.

+ 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: 0
    Last Post: 06-16-2015, 08:52 AM
  2. [SOLVED] Change text color of selected cells based on a text value of another cell
    By JNEWBURY2280 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-09-2014, 11:26 PM
  3. VB - Writes current month and previous months in cells
    By adgjqetuo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-17-2009, 06:07 PM
  4. Writing cells using Range() slows down with successive writes
    By ChucksFriend in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-04-2008, 09:25 AM
  5. ActiveX ComboBox selection writes multiple cells
    By darmstrong in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-30-2007, 10:31 PM
  6. [SOLVED] repetitive text imports
    By Knox in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2006, 04:45 PM
  7. [SOLVED] Speeding up writes to cells?
    By Bruce E. Stemplewski in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-30-2005, 08:06 PM

Tags for this Thread

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