# 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

• 15.2 KB Views: 7

#### Hui

##### Excel Ninja
Staff member
Do you max per Row, Column or the Whole area ?

#### John Lie Votacion

##### New Member
Do you max per Row, Column or the Whole area ?

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

• 17.2 KB Views: 9

#### 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!!