1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

find the Text with the highest number of duplicate text entries

Discussion in 'Ask an Excel Question' started by John Lie Votacion, Apr 20, 2017.

  1. John Lie Votacion

    John Lie Votacion New Member

    Messages:
    9
    I know its easy but i don't know what formula to use to "Find find the Text with the highest number of duplicate text entries"
    The result should be posted in Column N "Result"

    Thank you very much.

    Attached Files:

  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,387
    Do you max per Row, Column or the Whole area ?
    Please give an example result
  3. John Lie Votacion

    John Lie Votacion New Member

    Messages:
    9

    Hi Sir, as you can see with the attachment, In Cell M5, the result is "Earth Spirit" "Previous" Column since it is the one with the highest duplicate from cell "G5:L5"

    I want to know the formula wherein Earth spirit or the one with the most number of duplicate will directly be the result for all the remaining cell in the column.

    Thank you
    Last edited: Apr 20, 2017
  4. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,053
    In N5, array formula copy down :

    =IFERROR(INDEX(G5:L5,MODE(IF(G5:L5>0,MATCH(G5:L5,G5:L5,0)))),"No duplicate text entries")

    p.s. array formula to be confirmed pressing with SHIFT+CTRL+ENTER 3 keystrokes together

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose likes this.
  5. John Lie Votacion

    John Lie Votacion New Member

    Messages:
    9
    yay, It works!!! Thanks sire, this is great!
  6. rahulshewale1

    rahulshewale1 Member

    Messages:
    82
    Hii,

    you can use below formula also .

    try this in N5

    {=INDEX($G$5:$L$5,MATCH(MAX(COUNTIF(G5:L5,G5:L5)),COUNTIF(G5:L5,G5:L5),0))}

    Note:array formula to be confirmed pressing with SHIFT+CTRL+ENTER 3 keystrokes together


    Thanks
    rahul shewale
  7. John Lie Votacion

    John Lie Votacion New Member

    Messages:
    9
    Hi sir,

    Thank you very much for this, more power to you!!

Share This Page