Can you display the most common text value in a Column

L

leibale

Active Member

Joined

Sep 12, 2002

Messages

337

• Nov 16, 2004

• #2

fugeg70 said:

Hello all,

Is there a formula to show the most common text value in a column.

I am trying to get a simple of display of what areas people work in most?

Thanks,

Graham.

Click to expand...

try this:
=LOOKUP(MAX(COUNTIF(A1:A21,A1:A21)),COUNTIF(A1:A21,A1:A21),A1:A21)
where the values are in A1:A21

end with Shift Ctrl Enter

 

Upvote 0

L

leibale

Active Member

Joined

Sep 12, 2002

Messages

337

• Nov 18, 2004

• #3

Just let me if it is works and answere your problem

 

Upvote 0

F

fugeg70

Board Regular

Joined

Mar 26, 2003

Messages

114

• Nov 22, 2004

• #4

This works perfectly, thankyou very much!

G.

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Nov 22, 2004

• #5

fugeg70 said:

This works perfectly...

Click to expand...

I have to disagree...

aaModeOrMostFrequent fugeg70 after olly_w.xls
	A	B	C	D	E
1	Name	Count	Rank		1
2	XZA	2	1		1
3	XZA				MostFreq
4	SDA	2	2		XZA
5	CFA	1	3		SDA
6	SDA
7	XFA	1	4
8	PQA	1	5
9
10
Sheet1

Formulas...

B2, copied down:

=IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),"",COUNTIF($A$2:$A$8,A2))

C2, copied down:

=IF(N(B2),RANK(B2,$B$2:$B$8)+COUNTIF($B$2:B2,B2)-1,"")

E1 houses 1 (meaning: Top 1).

E2:

=MAX(IF(INDEX(B2:B8,MATCH(E1,C2:C8,0))=B2:B8,C2:C8))-E1

which must be confirmed with control+shift+enter instead of just with enter.

E4, copied down:

=IF(ROW()-ROW($E$4)+1<=$E$1+$E$2,INDEX($A$2:$A$8,MATCH(ROW()-ROW($E$4)+1,$C$2:$C$8,0)),"")

 

Upvote 0

J

jsussell

New Member

Joined

Dec 29, 2004

Messages

7

• Feb 4, 2005

• #6

How about to show not just the most frequently occurring text string (max) but to show the top 5 or 10 - would you replace the max function with the rank function within the array formula?>

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Feb 4, 2005

• #7

jsussell said:

How about to show not just the most frequently occurring text string (max) but to show the top 5 or 10 - would you replace the max function with the rank function within the array formula?>

Click to expand...

Just replace the parameter (Top N) specification in E1, which is 1 above, with 5 or 10.

 

Upvote 0

J

jsussell

New Member

Joined

Dec 29, 2004

Messages

7

• Feb 4, 2005

• #8

I was hoping to do this within the context of an array formula, because of the sheer volume of data, multiple formulae/cell spread out across the sheet would be pretty bulky. . . . any suggestions?

 

Upvote 0