Most Frequent Text String

**Bretto75** · 11-27-2008, 11:15 PM

Hi all

What im trying to do is get the most common (or frequent) text string from a range to display in another cell. eg a list of names Bob Barton, Carl Carry, etc where there names could be in a range of cells umteen times (eg sales record) so i know who sold the most for the month without using a count, i need the actual name in the new cell.

Any Ideas - i already tried using this formula:

=INDEX(sales,MATCH(MAX(COUNTIF(sales,sales)),COUNTIF(sales,sales),0))

but it returns a #N/A - a value is not avalible to the formula or function

could it be because im using a text srting in my values?

Brett

**Freaky_zoid** · 11-28-2008, 03:49 AM

Brett, I see the problem in using COUNTIF function. Try it separately, apart from the rest of the formula. It returns the number of duplicates of the value in the current row. So you need to copy it down to see this nimber of duplicates for each value. And you want your formula to find the maximum of these and then return the name.
You may also enter MAX(COUNTIF(sales,sales)) as an array formula and get the maximum number of duplicates, but if you try to use MATCH it won't work. So I suggest that you add a field with COUNTIF results, and then use your INDEX+MATCH formula search the maximum value and return the name.
Please try and say if this helps

**tigertiger** · 11-28-2008, 04:04 AM

Originally Posted by Bretto75

Hi all

What im trying to do is get the most common (or frequent) text string from a range to display in another cell. eg a list of names Bob Barton, Carl Carry, etc where there names could be in a range of cells umteen times (eg sales record) so i know who sold the most for the month without using a count, i need the actual name in the new cell.

Any Ideas - i already tried using this formula:

=INDEX(sales,MATCH(MAX(COUNTIF(sales,sales)),COUNTIF(sales,sales),0))

but it returns a #N/A - a value is not avalible to the formula or function

could it be because im using a text srting in my values?

Brett

The formula is OK, however you should use it in Array-Formula, That means you should press Ctrl+Shift+Enter after type or Edit this formula - it will add automatically two symbol { }

=INDEX(B16:B18,MATCH(MAX(COUNTIF(B16:B18,B16:B18)),COUNTIF(B16:B18,B16:B18)))

press Ctrl+Shift+Enter
becomes

{=INDEX(B16:B18,MATCH(MAX(COUNTIF(B16:B18,B16:B18)),COUNTIF(B16:B18,B16:B18)))}

**Richard Schollar** · 11-28-2008, 04:57 AM

Here's an alternative that doesn't require Ctrl+Shift+Enter (note that all formulas so far only return a singluar result, so if two people had the same number of maximum sales then only the first would be returned):

=INDEX(sales,MODE(MATCH(sales,sales,0)))

Richard

**tigertiger** · 11-28-2008, 05:03 AM

Originally Posted by RichardSchollar

Here's an alternative that doesn't require Ctrl+Shift+Enter (note that all formulas so far only return a singluar result, so if two people had the same number of maximum sales then only the first would be returned):

=INDEX(sales,MODE(MATCH(sales,sales,0)))

Richard

It is great! thanks for RichardSchollar

RichardSchollar! Do you think we can have a formula that list all sellers who get the same number of maximum sales?? How to do it?
.
Thanks in advance for all

**martindwilson** · 11-28-2008, 05:49 AM

tigertiger something wrong there if i apply the formula
{=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10)))}
to in b1:10

martin
martin
martin
martin
fred
fred
fred
chris
chris
chris
it returns chris not martin but =INDEX(sales,MODE(MATCH(sales,sales,0)))from richard does return martin

**Richard Schollar** · 11-28-2008, 05:54 AM

Martin

That formula needs the third argument of the MATCh function to be FALSE (or zero):

{=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),0))}

Richard

**tigertiger** · 11-28-2008, 06:05 AM

Originally Posted by martindwilson

tigertiger something wrong there if i apply the formula
{=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10)))}
to in b1:10

martin
martin
martin
martin
fred
fred
fred
chris
chris
chris
it returns chris not martin but =INDEX(sales,MODE(MATCH(sales,sales,0)))from richard does return martin

Oh, You should put the formula in only one cell not array of cells

**Richard Schollar** · 11-28-2008, 06:39 AM

Originally Posted by tigertiger

It is great! thanks for RichardSchollar

RichardSchollar! Do you think we can have a formula that list all sellers who get the same number of maximum sales?? How to do it?
.
Thanks in advance for all

I can get something to work but it is horrible (hopefully the Daddy will take a look at this thread and show us how to do it!):

Assume data in A1:A10

in B1 use formula:

=MODE(COUNTIF(A1:A10,A1:A10))

this gives the highest frequency

in B2 use this formula:

=SUMPRODUCT(--(COUNTIF(A1:A10,A1:A10)=B1))/B1

this gives the numer of instances of names in column A with the Mode as calculated in B1

Then in C1 copied down a few rows:

=IF(ROWS($A$1:$A1)>$B$2,"",INDEX($A$1:$A$10,LARGE((FREQUENCY(MATCH($A$1:$A$10,$A$1:$A$10,0),MATCH($A$1:$A$10,$A$1:$A$10,0))=$B$1)*(ROW($A$1:$A$11)-ROW($A$1)+1),ROWS($A$1:$A1))))

which is an array formula and so must be entered with Ctrl+Shift+Enter. Note that in the above, the red highlighted ROW() range is not a typo - it needs to be one greater in size than the actual data range.

Richard

**romperstomper** · 11-28-2008, 07:35 AM

I think at that point a UDF might be in order!

(subject of course to Daddy's intervention!)

**Richard Schollar** · 11-28-2008, 07:44 AM

Originally Posted by romperstomper

I think at that point a UDF might be in order!

Sigh. You're such a code junkie...

**romperstomper** · 11-28-2008, 08:14 AM

Which is easier to recognize when you come back to it in 6 months - your formula or =MAXLIST(A1:A10)?

**Richard Schollar** · 11-28-2008, 08:31 AM

Well if you're going to confuse the issue with logic and common sense...

**tigertiger** · 11-29-2008, 01:20 PM

Originally Posted by RichardSchollar

I can get something to work but it is horrible (hopefully the Daddy will take a look at this thread and show us how to do it!):

Assume data in A1:A10

in B1 use formula:

=MODE(COUNTIF(A1:A10,A1:A10))

this gives the highest frequency

in B2 use this formula:

=SUMPRODUCT(--(COUNTIF(A1:A10,A1:A10)=B1))/B1

this gives the numer of instances of names in column A with the Mode as calculated in B1

Then in C1 copied down a few rows:

=IF(ROWS($A$1:$A1)>$B$2,"",INDEX($A$1:$A$10,LARGE((FREQUENCY(MATCH($A$1:$A$10,$A$1:$A$10,0),MATCH($A$1:$A$10,$A$1:$A$10,0))=$B$1)*(ROW($A$1:$A$11)-ROW($A$1)+1),ROWS($A$1:$A1))))

which is an array formula and so must be entered with Ctrl+Shift+Enter. Note that in the above, the red highlighted ROW() range is not a typo - it needs to be one greater in size than the actual data range.

Richard

A good respond,
I think that we should define some names, it will be easier

**martindwilson** · 11-29-2008, 01:25 PM

tiger i did put it in one cell i was using it to look at the range b1:b10
and it did not work if had a bit missing see richards reply after my post

**tigertiger** · 11-29-2008, 01:34 PM

Originally Posted by martindwilson

tiger i did put it in one cell i was using it to look at the range b1:b10
and it did not work if had a bit missing see richards reply after my post

OK, I had seen that post of richards ...
What about richards...'s comment? have you try to do it?
?

**martindwilson** · 11-29-2008, 02:10 PM

your formula
=INDEX(B16:B18,MATCH(MAX(COUNTIF(B16:B18,B16:B18)),COUNTIF(B16:B18,B16:B18)))
richards correction
=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),0))

**tigertiger** · 11-30-2008, 01:50 AM

Originally Posted by martindwilson

your formula
=INDEX(B16:B18,MATCH(MAX(COUNTIF(B16:B18,B16:B18)),COUNTIF(B16:B18,B16:B18)))
richards correction
=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),0))

I see, tks you
When we add the parameter (0) that mean Match find the exact information

**Bretto75** · 11-30-2008, 10:52 PM

Well thanks for all the replies i will have to look over them and see what works

I did notice that there was an error about merged cells etc.. also im using a data list to display the names - would that cause any problems?

But thanks again everyone i will try your suggestions and get back with what worked

Brett

Well i got it to work using richards correction above in martins post - thanks alot

**Wrathchild** · 05-13-2009, 02:35 PM

For some reason I can't see my post to this thread or ContaminatedWitExcel's reply. I only know that he wrote something because I received an email about it. The formula I ended up using was

{=INDEX(A3:A39,MODE(IF(A3:A39<>"",MATCH(A3:A39,A3:A39,0))))}

However, I have directly below that data a new group of rows running from row 41 to 58. When I try to put that same formula in row 59 as

{=INDEX(A41:A58,MODE(IF(A41:A58<>"",MATCH(A41:A58,A41:A58,0))))}

I get #N/A in all of the cells. Why would this formula work on the first set of data but not the second?

**Wrathchild** · 05-13-2009, 02:36 PM

FWIW, I can see my newly posted messages at the end of the thread after posting them, but after refreshing I can still only see the last message from last year.

**Domenic** · 05-13-2009, 02:39 PM

Originally Posted by Wrathchild

For some reason I can't see my post to this thread or ContaminatedWitExcel's reply. I only know that he wrote something because I received an email about it. The formula I ended up using was

{=INDEX(A3:A39,MODE(IF(A3:A39<>"",MATCH(A3:A39,A3:A39,0))))}

However, I have directly below that data a new group of rows running from row 41 to 58. When I try to put that same formula in row 59 as

{=INDEX(A41:A58,MODE(IF(A41:A58<>"",MATCH(A41:A58,A41:A58,0))))}

I get #N/A in all of the cells. Why would this formula work on the first set of data but not the second?

Unless the data has one value occurring more than once, the formula will return #N/A. Is this the case?

**Wrathchild** · 05-13-2009, 02:48 PM

Replying to Domenic (still can't see the messages):

In reviewing the data that's exactly what is happening in those particular ranges. Thanks for pointing that out. I feel much better now.

**Wrathchild** · 06-01-2009, 12:42 PM

Originally Posted by Domenic

Unless the data has one value occurring more than once, the formula will return #N/A. Is this the case?

You know, I've been using this formula all this time and it looks like it was tricking me. It appears that if more than one value have the same number of occurrences it will pick one of them as the result of the formula. Is this correct?