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.

 

[james.waller]

Your suggestion worked a treat, Aladin. Thank you. One last problem, the list is displaying a surplus entry at the bottom with a count of one. This is despite the list supposedly filtering out all results with a count of less than 2. Is there a fix for this?

 

[Aladin Akyurek]

Your suggestion worked a treat, Aladin. Thank you. One last problem, the list is displaying a surplus entry at the bottom with a count of one. This is despite the list supposedly filtering out all results with a count of less than 2. Is there a fix for this?

The COUNT formula (see my 2nd exhibit much earlier) also needs to be adjusted...

=COUNT(IF(FREQUENCY(IF($X!$AA$2:$AA$274<>"",IF(X!$AA$2:$AA$274<>”N/A”,MATCH("~"&X!$AA$2:$AA$274,X!$AA$2:$AA$274&"",0))),Rvec)>=2,1))

which requires control+shift+enter.

 

[james.waller]

It worked! I am forever in your gratitude! Thank you so very very much!

 

[james.waller]

Aladin,
I keep coming up with further refinements for your formula. How I wish I had your Excel skills, haha. Irrespective, if you could once again assist me I would be eternally grateful.

I wish to incorporate the following two formulas into your three original formulas:
IF($B$3="All", 1, IF('Retail Branch Property Database'!$C$2:$C$274=$B$3, 1, 0))
IF($B$4="All", 1, IF('Retail Branch Property Database'!$I$2:$I$274=$B$4, 1, 0))

Original Forumla 1 (Array) - Total Count:
=COUNT(IF(FREQUENCY(IF('Retail Branch Property Database'!$AB$2:$AB$274<>"",IF('Retail Branch Property Database'!$AB$2:$AB$274<>"N/A",MATCH("~"&'Retail Branch Property Database'!$AB$2:$AB$274,'Retail Branch Property Database'!$AB$2:$AB$274&"",0))),Rvec)>=2,1))

Original Formula 2 (Array) - Item Name:
=IF(N(Q7),INDEX('Retail Branch Property Database'!$AB$2:$AB$274,SMALL(IF(FREQUENCY(IF('Retail Branch Property Database'!$AB$2:$AB$274<>"",IF('Retail Branch Property Database'!$AB$2:$AB$274<>"N/A",MATCH("~"&'Retail Branch Property Database'!$AB$2:$AB$274,'Retail Branch Property Database'!$AB$2:$AB$274&"",0))),Rvec)=Q7,Rvec),COUNTIF($Q$7:Q7,Q7))),"")

Original Formula 3 (Array) - Item Count:
=IF(ROWS($Q$7:Q7)<=$P$5,LARGE(FREQUENCY(IF('Retail Branch Property Database'!$AB$2:$AB$274<>"",IF('Retail Branch Property Database'!$AB$2:$AB$274<>"N/A",MATCH("~"&'Retail Branch Property Database'!$AB$2:$AB$274,'Retail Branch Property Database'!$AB$2:$AB$274&"",0))),Rvec),ROWS($Q$7:Q7)),"")

Rvec:
=ROW('Retail Branch Property Database'!$AB$2:$AB$274)-ROW('Retail Branch Property Database'!$AB$2)+1

How would I go about doing this?

Thanks,
James

 

[james.waller]

To give you an idea of how I wish to merge the formulas together, I am currently working with the following for the Total Count formula:

=COUNT(IF(FREQUENCY(IF('Retail Branch Property Database'!$AB$2:$AB$274<>"",IF('Retail Branch Property Database'!$AB$2:$AB$274<>"N/A",IF($B$3="All", IF($B$4="All", MATCH("~"&'Retail Branch Property Database'!$AB$2:$AB$274,'Retail Branch Property Database'!$AB$2:$AB$274&"",0), IF('Retail Branch Property Database'!$I$2:$I$274=$B$4, MATCH("~"&'Retail Branch Property Database'!$AB$2:$AB$274,'Retail Branch Property Database'!$AB$2:$AB$274&"",0))), IF('Retail Branch Property Database'!$C$2:$C$274=$B$3, IF($B$4="All", MATCH("~"&'Retail Branch Property Database'!$AB$2:$AB$274,'Retail Branch Property Database'!$AB$2:$AB$274&"",0), IF('Retail Branch Property Database'!$I$2:$I$274=$B$4, MATCH("~"&'Retail Branch Property Database'!$AB$2:$AB$274,'Retail Branch Property Database'!$AB$2:$AB$274&"",0))))))),Rvec)>=2,1))

I have, thus far, been unable to get such a formula to work. Any idea as to where I'm going wrong?

 

[id4excel]

Hey James, I know it is a old thread. just want to know did you got the solution. I am also looking for similar solution.

 

[Aladin Akyurek]

Hey James, I know it is a old thread. just want to know did you got the solution. I am also looking for similar solution.

Could you describe the problem you want to solve?

 

[id4excel]

Thanks for the reply Aladin Sir,I was thinking to message you about the the same. The solution is provided by "hrlngrv" Harlan Grove most frequent string in a column report - Excel User Group

I have used it in my file (attached) and it works beautifully. Zippyshare.com - id4excel_2.xls

Sir, I am also looking for a solution to other fields of my dashboard. Please take a look at attached file.

 

[Aladin Akyurek]

Thanks for the reply Aladin Sir,I was thinking to message you about the the same. The solution is provided by "hrlngrv" Harlan Grove most frequent string in a column report - Excel User Group

I have used it in my file (attached) and it works beautifully. Zippyshare.com - id4excel_2.xls

Sir, I am also looking for a solution to other fields of my dashboard. Please take a look at attached file.

I'd suggest to separate the data and the results (the dashboard) that you want to show.

What are the differences between these three items/requests?

Top 5 Vendors (based on number of orders)

Top 5 vendors order numbers

Top 5 Vendors (based on amount of orders)

<tbody>
</tbody>

 

[id4excel]

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 <colgroup><col><col><col><col><col span="2"></colgroup><tbody> </tbody> <colgroup><col><col><col><col><col span="2"></colgroup><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.