Text with maximum frequency per row

[tarun]

Hi,


I have got multiple names in each rows and would like to have what name is repeated maximum number of times and how many times?


Eg. Ram, Amitabh, Obama, Ram, William, Ram

Ans: Ram (3 times)


Data is very comoplicated, (having various formulaes) spread across hundreds of columns & thousands of rows and its too heavy to do Pivot.


-Thanks

 

[keymaster]

@Tarun...


interesting question. Assuming your data is in row 2, in the range, B2:K2, in an empty cell write,

[pre]

=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0)) & " ("&MAX(COUNTIF(B2:K2,B2:K2))&" times)"

[/pre]

and press CTRL+SHIFT+Enter to see the name & total number of it repeated.


You can speedup & simplify the formula by using a named formula (relative of course) for the COUNTIF(B2:K2,B2:K2) part.

 

[Hui]

@Tarun

This can be simplified slightly by removing the Max function

=INDEX(B2:K2,MATCH(COUNTIF(B2:K2,B2:K2), COUNTIF(B2:K2,B2:K2),0)) & " ("&COUNTIF(B2:K2,B2:K2)&" times)"

Ctrl Shift Enter

 

[Jack]

Hey Hui I have been following this one with interest as I have a practical use for it. Chandoo's formula returns the highest repeat name no matter the order - your variation is returning the name used first although not the highest repeating.


fred fred bill bill bill bill fred


Hui fred (3 times)

C bill (4 times)


John

 

[Hui]

Thanx John

I hadn't followed through on this as I was distracted on another job.

 

[wlerner]

This works wonderfully! However, what if I wanted more than the top name, instead I wanted the top 10, or 20? How would this be modified to accomplish this? Oh, and what if your data were in columns? How about if there were a tie?


Thank you!

 

[NARAYANK991]

Hi ,


Let us assume your row of data is named List ; let us assume that your data extends over 10 columns , from column A through column J ; let us also assume that the formulae for listing the 10 most frequent occurring text entries are in cells B20 through K20.


Enter the following formula , in B20 , and copy across , till K20 , entering it as an array formula , using CTRL SHIFT ENTER :


=INDEX(List, MATCH(IF(MAX(COUNTIF(List, List)*IF(COUNTIF($A20:A20, List)=1, 0, 1))=0, 1, MAX(COUNTIF(List, List)*IF(COUNTIF($A20:A20, List)=1, 0, 1))), COUNTIF(List, List)*IF(COUNTIF($A20:A20, List)=1, 0, 1), 0))


Ensure that A20 does not contain any text which also figures in your row of data.


Taken from : http://www.get-digital-help.com/2009/09/24/unique-distinct-list-sorted-based-on-occurrance-in-a-column-in-excel/


Narayan

 

[Guest]

"It's been a while since I've been on FB & I thought I'd drop by to let you all in on some exciting news>Honor is going to be a Big Sister,air jordan heels! shorts,retro jordan, Instead wear moderately loose and longer lengths in thicker fabrics to give a longer and leaner look Other Tips Rule. The clothes of unisex style are in great demand today because these are practical,air jordan retro shoes, unisex style is the style of future due to the fact that people started to choose simple and comfortable clothing. You can take a pick from wholesale dresses,cheap jordans,You can buy cheap ladies wholesale clothing at asianfashion4u
it is now an impossible feat to loose what the Italian fashion UK industry along with other fashion ethics have given to us in the last 100 years.With a chic purse or dazzling jewelry For instance.

Related articles：

• ralphlauren,Not are all endowed like Russell Burns

 

[gaurav052]

Hi,


Can you help me to know how we can add scrolling text box while replying to somebody with some code on any post.Suppose i want to reply to somebody with vba code of 100 lines .So i want to add a scrolling text box in which i can copy the code to appear as a neat post.


Thanks,

Gaurav