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 at 6:54 AM.

1. ### John Lie VotacionNew 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.

File size:
15.2 KB
Views:
6
2. ### HuiExcel NinjaStaff Member

Messages:
10,277
Do you max per Row, Column or the Whole area ?
3. ### John Lie VotacionNew 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 at 11:32 AM
4. ### bosco_yipWell-Known Member

Messages:
875
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:

• ###### Kiev.xlsx
File size:
17.2 KB
Views:
6
Thomas Kuriakose likes this.
5. ### John Lie VotacionNew Member

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

Messages:
81
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 VotacionNew Member

Messages:
9
Hi sir,

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