• 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...

  • 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

John Lie Votacion

New Member
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.
 

Attachments

Hui

Excel Ninja
Staff member
Do you max per Row, Column or the Whole area ?
Please give an example result
 

John Lie Votacion

New Member
Do you max per Row, Column or the Whole area ?
Please give an example result

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:

bosco_yip

Excel Ninja
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
 

Attachments

John Lie Votacion

New Member
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
yay, It works!!! Thanks sire, this is great!
 

rahulshewale1

Active Member
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
 

John Lie Votacion

New Member
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
Hi sir,

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