Problem: 1st, 2nd, 3rd most common text

[nickmontreal]

Hi all,

I have a home made database of client information and working to create meaningful statistics with it. No doubt I will continue to post my questions on the forum as I am an Excel Novice. Here is the current problem:

Trying to find 1st, 2nd, and 3rd most common text in a column. Currently using this array formula to find MOST common text without issue. =INDEX(O7:O9998,MATCH(MAX(COUNTIF(O7:O9998,O7:O9998)),COUNTIF(O7:O9998,O7:O9998),0))

However, every formula I use to find 2nd and 3rd most common text create errors. Please help!

Many thanks,

 

[Marcílio_Lobão]

nickmontreal, Good evening.

Maybe someone will provide a better solution making good use of Excel's statistical functions.

It may be that also appear a good solution in VBA Excel.

I'm sorry, but this solution here is not very elegant, but by my tests it worked.

Perhaps you can use it until some better formula.

They are array formulas.
Attention to the use of the cell of the above formula in the subsequent formulas.

Try to do: (ARRAY Formulas)
T1 --> =INDEX(O7:O9998, MATCH(LARGE(COUNTIF(O7:O9998, O7:O9998), 1 ), COUNTIF(O7:O9998, O7:O9998), 0))

T2 --> =INDEX(O7:O9998, MATCH(LARGE(COUNTIF(O7:O9998, O7:O9998), COUNTIF(O7:O9998, T1) + 1 ), COUNTIF(O7:O9998, O7:O9998) ,0))

T3 --> =INDEX(O7:O9998, MATCH(LARGE(COUNTIF(O7:O9998, O7:O9998), COUNTIF(O7:O9998, T1) + COUNTIF(O7:O9998, T2) + 1 ), COUNTIF(O7:O9998, O7:O9998) ,0))


Is that what you want?
I hope it helps.

 

[nickmontreal]

Truly appreciate the help - BUT - ultimately unsuccessful. Each entry produces the same text.

More information: the column title is Country of Origin; the goal is to find the top 3 countries then later attach time stamps to them to extrapolate immigration rates by month; O5 uses array formula =INDEX(O7:O9998,MATCH(MAX(COUNTIF(O7:O9998,O7:O9998)),COUNTIF(O7:O9998,O7:O9998),0)) and successfully produces the result "Canada". If I limit the formula to =INDEX(O7:O50,MATCH(MAX(COUNTIF(O7:O50,O7:O50)),COUNTIF(O7:O50,O7:O50),0)) it will successfully produce the 2nd most common text. However, I need data for the entire column to be evaluated AND even replicating the formula to obtain the 3rd result only cycles between the 1st and 2nd (Canada and Mexico) in a loop.

I have crawled many forums seeking answers but to no avail. If anyone can suggest an alternative way to get these results I am looking for I have no issue changing strategies.

 

[Marcílio_Lobão]

nickmontreal, Good evening.

I did an example for you using the formulas that a gave to you before.

Take a look at it.

https://www.sendspace.com/file/zl4f6m

Remember: All the formulas are ARRAY

Is that what you want.
I hope it helps.

 

[nickmontreal]

Wow, thanks for continuing to help! I successfully retrieved the formulas from your spreadsheet but cannot get them to work correctly. Again - each entry produces the same result "Canada".

In formulas: =INDEX(O7:O9998, MATCH(LARGE(COUNTIF(O7:O9998, O7:O9998), COUNTIF(O7:O9998, T1) +1 ), COUNTIF(O7:O9998, O7:O9998) ,0)) why do you have column "T" included?

 

[Fazza]

what about using a pivot table?

put the country as a data field - it should default to count of countries - and row field. for the row field, set to show top 3

cheers

 

[nickmontreal]

I have a level zero skill with pivot tables. Anyway - wouldn't I need to create a list of all countries? This is impractical for our purpose because new people come from all over the world without notice.

Any other suggestions? Is there a formula to extract text and create a table? That would produce instant results for top 3 countries of origin and I could simply create a cell reference to keep track of data.

 

[Marcílio_Lobão]

nickmontreal, Good afternoon.

The letter T in the text of the formulas outside the cell is just to make the formula clear and was typed by mistake.
It should be the letter G as shown by the indicator for each formula.

If you click on cells G1, G2 and G3 you can see the correct formula with the letter G.

But even with my example for you, you said that the question continues to present error.

So the only alternative for me to continue trying to help you, is you save your spreadsheet on a free website, www.sendspace.com and put the download link here.
Remember to show which cells will be analyzed and what the expected result should be.

Only then can we really understand what your spreadsheet layout looks like.
Help us to help you.

We're waiting for your spreadsheet.

 

[nickmontreal]

nickmontreal, Good afternoon.

The letter T in the text of the formulas outside the cell is just to make the formula clear and was typed by mistake.
It should be the letter G as shown by the indicator for each formula.

If you click on cells G1, G2 and G3 you can see the correct formula with the letter G.

But even with my example for you, you said that the question continues to present error.

So the only alternative for me to continue trying to help you, is you save your spreadsheet on a free website, www.sendspace.com and put the download link here.
Remember to show which cells will be analyzed and what the expected result should be.

Only then can we really understand what your spreadsheet layout looks like.
Help us to help you.

We're waiting for your spreadsheet.

After that small correction and a bit of play it is working now. Thank you so much!

 

[Marcílio_Lobão]

Thanks for the feedback.

Glad to be of help.