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]

Aladin,
Turns out your formula works well in all but one instance. I am trying to use the following dynamic range reference in the F5 formula, (INDIRECT("'SGB Branch Property Database'!$Z$2:"&ADDRESS($B$9+1,26))) in place of your $A$3:$A$22.

The entire formula looks like this:
=IF(N(O4),INDEX((INDIRECT("'SGB Branch Property Database'!$Z$2:"&ADDRESS($B$9+1,26))),SMALL(IF(FREQUENCY(IF((INDIRECT("'SGB Branch Property Database'!$Z$2:"&ADDRESS($B$9+1,26)))<>"",
MATCH("~"&(INDIRECT("'SGB Branch Property Database'!$Z$2:"&ADDRESS($B$9+1,26))),(INDIRECT("'SGB Branch Property Database'!$Z$2:"&ADDRESS($B$9+1,26)))&"",0)),
ROW((INDIRECT("'SGB Branch Property Database'!$Z$2:"&ADDRESS($B$9+1,26))))-ROW('SGB Branch Property Database'!$Z$2)+1)=O4,
ROW((INDIRECT("'SGB Branch Property Database'!$Z$2:"&ADDRESS($B$9+1,26))))-ROW('SGB Branch Property Database'!$Z$2)+1),COUNTIF($O$4:O4,O4))),"")

However, excel claims this formula causes an error. I am guessing this is between the MATCH function and the ADDRESS function. How can the formula be edited to work-around this problem?

Cheers,
James

 

[Aladin Akyurek]

Aladin,
Turns out your formula works well in all but one instance.

...

Cheers,
James

Which instance is that?

It's probably much better to use a dynamic named range instead of invoking INDIRECT. What is the current range on SGB Branch Property Database?

 

[james.waller]

Aladin,
My apologies for my poor choice of wording before, which no doubt added to your confusion. By instance I meant the formula works when I use other forms of referencing. However, for reasons too complicated to explain here, I need to reference using the method already detailed. Is there a work-around to stop the MATCH function clashing with the ADDRESS function?

I know that the problem is caused by those two functions clashing because the following adaptation causes no errors:
=IF(N(O4),INDEX((INDIRECT("'SGB Branch Property Database'!$Z$2:"&ADDRESS($B$9+1,26))),SMALL(IF(FREQUENCY(IF((INDIRECT("'SGB Branch Property Database'!$Z$2:"&ADDRESS($B$9+1,26)))<>"", MATCH("~"&'SGB Branch Property Database'!$Z$2:$Z$275,'SGB Branch Property Database'!$Z$2:$Z$275&"",0)), ROW((INDIRECT("'SGB Branch Property Database'!$Z$2:"&ADDRESS($B$9+1,26))))-ROW('SGB Branch Property Database'!$Z$2)+1)=O4, ROW((INDIRECT("'SGB Branch Property Database'!$Z$2:"&ADDRESS($B$9+1,26))))-ROW('SGB Branch Property Database'!$Z$2)+1),COUNTIF($O$4:O4,O4))),"")

I still wish to have cell B9 influence the range (the purpose of my existing INDIRECT/ADDRESS function). How can I alter the above to remove the 'SGB Branch Property Database'!$Z$2:$Z$275 referencing whilst ensuring the formula still works?

Hopefully that is clearer,
James

 

[james.waller]

No ideas?

 

[james.waller]

Aladin,
I know this thread is now a month old but I have a follow up question. Assuming the following two formulas are encased in array brackets:

Name:
=IF(N(O4),INDEX(('Retail Branch Property Database'!$AA$2:$AA$274),SMALL(IF(FREQUENCY(IF(('Retail Branch Property Database'!$AA$2:$AA$274)<>"", MATCH("~"&'Retail Branch Property Database'!$AA$2:$AA$274,'Retail Branch Property Database'!$AA$2:$AA$274&"",0)), ROW(('Retail Branch Property Database'!$AA$2:$AA$274))-ROW('Retail Branch Property Database'!$AA$2)+1)=O4, ROW(('Retail Branch Property Database'!$AA$2:$AA$274))-ROW('Retail Branch Property Database'!$AA$2)+1),COUNTIF($O$4:O4,O4))),"")

Count:
=IF(ROWS($O$4:O4)<=$N$2,LARGE(FREQUENCY(IF(('Retail Branch Property Database'!$AA$2:$AA$274)<>"", MATCH("~"&('Retail Branch Property Database'!$AA$2:$AA$274),('Retail Branch Property Database'!$AA$2:$AA$274)&"",0)), ROW(('Retail Branch Property Database'!$AA$2:$AA$274))-ROW('Retail Branch Property Database'!$AA$2)+1),ROWS($O$4:O4)),"")

How do I go about excluding all fields containing the entry "N/A"? It is appearing as the most common response but, clearly, this is not an answer that's important.

 

[Aladin Akyurek]

Aladin,
I know this thread is now a month old but I have a follow up question. Assuming the following two formulas are encased in array brackets:

Name:
=IF(N(O4),INDEX(('Retail Branch Property Database'!$AA$2:$AA$274),SMALL(IF(FREQUENCY(IF(('Retail Branch Property Database'!$AA$2:$AA$274)<>"", MATCH("~"&'Retail Branch Property Database'!$AA$2:$AA$274,'Retail Branch Property Database'!$AA$2:$AA$274&"",0)), ROW(('Retail Branch Property Database'!$AA$2:$AA$274))-ROW('Retail Branch Property Database'!$AA$2)+1)=O4, ROW(('Retail Branch Property Database'!$AA$2:$AA$274))-ROW('Retail Branch Property Database'!$AA$2)+1),COUNTIF($O$4:O4,O4))),"")

Count:
=IF(ROWS($O$4:O4)<=$N$2,LARGE(FREQUENCY(IF(('Retail Branch Property Database'!$AA$2:$AA$274)<>"", MATCH("~"&('Retail Branch Property Database'!$AA$2:$AA$274),('Retail Branch Property Database'!$AA$2:$AA$274)&"",0)), ROW(('Retail Branch Property Database'!$AA$2:$AA$274))-ROW('Retail Branch Property Database'!$AA$2)+1),ROWS($O$4:O4)),"")

How do I go about excluding all fields containing the entry "N/A"? It is appearing as the most common response but, clearly, this is not an answer that's important.

Lets define Rvec using Insert|Name|Define as referring to:

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

The formulas as you have them become...

Let X stand for 'Retail Branch Property Database' in what follows and add the test for "N/A", not for "#N/A"...

=IF(N(O4),INDEX(X!$AA$2:$AA$274,
   SMALL(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)=O4,Rvec),
       COUNTIF($O$4:O4,O4))),"")

=IF(ROWS($O$4:O4)<=$N$2,LARGE(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),ROWS($O$4:O4)),"")

 

[james.waller]

Thanks Aladin, but unfortunately the first formula (name) is generating a #NUM value as the first response, whilst the second formula (count) comes up with the error message "You entered too few arguments for this function" when I attempt to enter it in Excel. Any thoughts?

 

[Aladin Akyurek]

Thanks Aladin, but unfortunately the first formula (name) is generating a #NUM value as the first response, whilst the second formula (count) comes up with the error message "You entered too few arguments for this function" when I attempt to enter it in Excel. Any thoughts?

A second attempt...

Name:

1) Change the sheet name Retail Branch Property Database to X in your workbook.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

2) Define Rvec is defined as referring to:<o></o>
<o> </o>
=ROW(X!$AA$2:$AA$274)-ROW(X!$AA$2)+1<o></o>
<o> </o>
3) Type the following formula in the appropriate cell:
<o> </o>
=IF(N(O4),INDEX(X!$AA$2:$AA$274,SMALL(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)=O4,Rvec),COUNTIF($O$4:O4,O4))),"")

4) Now confirm the formula with control+shift+enter.

Count:

5) Type the following formula in the appropriate cell:

=IF(ROWS($O$4:O4)<=$N$2,LARGE(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),ROWS($O$4:O4)),"")

6) Now confirm this formula with control+shift+enter.

7) If all is well, set the sheet name in your workbook back to: Retail Branch Property Database.

 

[james.waller]

It won't let me enter the formula, for both it says "The formula you typed contains an error" and highlights the "N portion of "N/A".

 

[Aladin Akyurek]

It won't let me enter the formula, for both it says "The formula you typed contains an error" and highlights the "N portion of "N/A".

Would you check whether you typed " a double quote, not twice ', which is a single quote?