Generating A List Of The Five Most Common Responses (Text-Based Mode)

[james.waller]

I should stress, prior to explaining, that I am seeking a function-based solution to this problem. I am aware I could solve this conundrum using either VBA or a pivot table but, for reasons too complex to detail, this will not do in this instance.

I would like to generate a dynamic list of the top five most common text responses in a range. For simplicity sake, let's assume that the range spans cells A1 to A20, and the list is generated in cells B1 to B5. So, essentially, I am seeking five separate formulas. One to calculate the most common text element, one to calculate the second most common text element, one to calculate the third most common text element and so forth.

I am currently working with the two formulas below but finding it incredibly difficult to merge them into a working format.

Formula for finding second most common number:
=MODE(IF(IF(A1:A20<>"",A1:A20)<>MODE(IF(A1:A20<>"",A1:A20)
),IF(A1:A20<>"",A1:A20),""))

Formula for finding most common text element:
=INDEX(A1:A20,MODE(IF(ISTEXT(A1:A20
),MATCH(A1:A20,A1:A20,0))))

Any help regarding the matter is greatly appreciated.

 

[Aladin Akyurek]

Top 5 Vendors (based on number of orders)- Every time a vendor book an order, he does an entry. Ram has booked 29 orders, hence his name appears (entered by him) 29 times in list. (number of Ram name occurrence = number of orders by him) <tbody> </tbody> Dashboard Top 5 Vendors (based on number of orders) Ram Bob Jen Sen Rob Top 5 vendors order numbers 29 25 17 10 1 <tbody> </tbody> <tbody> </tbody>

Top 5 vendors order numbers- Ram has booked order 29 time hence his "order numbers" are 29. So for Bob its 25 who is in top 5 (#2nd position)

Top 5 Vendors (based on amount of orders)- Now this query is similar to the first query, but here instead of order numbers (occurrence of a particular name) the Top 5 will be based on (Sum of his order booked Amount) Here again Ram has topped the list with amount 116439, and Bob is on second place with 104853.

<tbody>
</tbody>

Hope I am clear.

We will tackle the whole in pieces...

Define Ivec (short for integer vector) as referring to:

=ROW(Excel2007_Dashboard!$A$5:$A$91)-ROW(Excel2007_Dashboard!$A$5)+1

Insert a column after column H. Not yet complete dashboard in I:Q will look like below:

Counts	Dashboard
5	Top 5 Vendors (based on number of orders)	Ram	Bob	Jen	Sen	Rob	Mik
6	Top 5 vendors order numbers	30	26	19	10	1	1
5	Top 5 Vendors (based on amount of orders)	Jen	Rob	Jen	Jen	Ram	Ram	Ram
7	Top 5 vendors order amount	5467	4590	4590	4590	4590	4590	4590
5	Top 5 Vendors (based on total booking days)	Rob	Jen	Jen	Ram	Ram	Ram	Ram
7	Top 5 total booking days	9	9	9	9	9	9	9
	Number of unique vendors	6
	Number of unique hotels	9

<tbody>
</tbody>

I5: 5

K5, control+shift+enter (CSE), not just enter, copy across:

=IF(K$6="","",INDEX($A$5:$A$91,SMALL(IF(FREQUENCY(IF($A$5:$A$91<>"",
  MATCH($A$5:$A$91,$A$5:$A$91,0)),Ivec)=K$6,Ivec),COUNTIF($K$6:K$6,K$6))))

I6, CSE:

=SUM(IF(FREQUENCY(IF($A$5:$A$91<>"",
  MATCH($A$5:$A$91,$A$5:$A$91,0)),Ivec)>=LARGE(FREQUENCY(IF($A$5:$A$91<>"",
  MATCH($A$5:$A$91,$A$5:$A$91,0)),Ivec),I5),1))

K6, CSE and copy across:

=IF(COLUMNS($K$6:K$6)<=$I$6,LARGE(FREQUENCY(IF($A$5:$A$91<>"",
  MATCH($A$5:$A$91,$A$5:$A$91,0)),Ivec),COLUMNS($K$6:K$6)),"")

I7: 5

K7, CSE and copy across:

=IF(K$8="","",INDEX($A$5:$A$91,SMALL(IF($H$5:$H$91=K$8,Ivec),
  COUNTIF($K$8:K$8,K$8))))

I8, just enter:

=COUNTIF(H5:H91,">="&LARGE(H5:H91,I7))

K8, just enter and copy across:

=IF(COLUMNS($K$8:K$8)<=$I$8,LARGE($H$5:$H$91,COLUMNS($K$8:K$8)),"")

I9: 5

K9, CSE and copy across:

=IF(K$10="","",INDEX($A$5:$A$91,SMALL(IF($E$5:$E$91=K$10,Ivec),
  COUNTIF($K$10:K$10,K$10))))

I10, just enter:

=MIN($I$8,COUNTIF(E5:E91,">="&LARGE(E5:E91,I9)))

K10, just enter and copy across:

=IF(COLUMNS($K$10:K$10)<=$I$10,LARGE($E$5:$E$91,COLUMNS($K$10:K$10)),"")

K11, CSE:

=SUM(IF(FREQUENCY(IF(A5:A91<>"",MATCH("~"&A5:A91,A5:A91&"",0)),Ivec),1))

K12, CSE:

=SUM(IF(FREQUENCY(IF(B5:B91<>"",MATCH("~"&B5:B91,B5:B91&"",0)),Ivec),1))

 

[id4excel]

Thank you sir for your time and efforts. I have few quires.

What job Ivec doing in the formula?
My apology Sir, I am not a science graduate, hence the comprehension deficiency I am facing to understand the excel engineering formulas and functions.

Also, I want to take advice, which method is better approach? Clean single cell formula or should I go for helper column or rows then simple formulas to achieve the result.

I am saying this as if asked, I should be able to explain how the formula is fetching the result.

Thanks and Regards

 

[Aladin Akyurek]

Thank you sir for your time and efforts. I have few quires.

What job Ivec doing in the formula?
My apology Sir, I am not a science graduate, hence the comprehension deficiency I am facing to understand the excel engineering formulas and functions.

Also, I want to take advice, which method is better approach? Clean single cell formula or should I go for helper column or rows then simple formulas to achieve the result.

I am saying this as if asked, I should be able to explain how the formula is fetching the result.

Thanks and Regards

It's already a tall order. Explanations in between will quadruple the efforts... If you want to understand some bits of a formula, select that bit on the formula bar and hit F9. For example, select the Ivec bit, hit F9 to see to what it evaluates. By the way, the bit is used as a set of bins a frequency formula needs.

Among the remaining requests, some are repeats (that is, they are already covered in the post I made above), some are not very useful to have, those first week specs, for example.

Here is the file so far:

https://dl.dropboxusercontent.com/u/65698317/aaTop5MostCommonTextAndOtherCalcs id4excel_2.xls

 

[id4excel]

Thank you for all your help.
I will look into it and try to understand each in bits. Also want to know, as some one suggesting me that pivot table should be able to achieve all these queries with relative ease. Is it so?

I have never used pivot table before, but if it can achieve all in a similar dashboard or a good chunk of it. Then I will give them a try and will learn to make reports via pivot tables.

Once again thank you.

 

[Aladin Akyurek]

Thank you for all your help.
I will look into it and try to understand each in bits. Also want to know, as some one suggesting me that pivot table should be able to achieve all these queries with relative ease. Is it so?

I have never used pivot table before, but if it can achieve all in a similar dashboard or a good chunk of it. Then I will give them a try and will learn to make reports via pivot tables.

If they say so...

Once again thank you.

You are welcome.