batteredveg
New Member
- Joined
- Aug 27, 2014
- Messages
- 7
Hi Folks
Would be grateful of any advice.
I'm trying to generate a 'Top 5' of the most frequently occurring text strings in a column.
In Column A is a list of countries:
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Germany[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Germany[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Italy[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]United States of America[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Ireland[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
In another column, without the use of pivot tables or filters, I'd like to have a Top 5 list which will list, in the correct order, the most frequently occurring text strings, like this:
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]
[TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Top 5[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Germany[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]United Kingdom[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Italy[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]United States of America[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]France[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]
Can anyone suggest a formula which would do the job? Nothing I've found via Google seems to do the job.
Much obliged,
Paul
Would be grateful of any advice.
I'm trying to generate a 'Top 5' of the most frequently occurring text strings in a column.
In Column A is a list of countries:
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]
A
[/TD][/TR]
[TR]
[TD]1[/TD]
[TD]Germany[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Germany[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Italy[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]United States of America[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Ireland[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
In another column, without the use of pivot tables or filters, I'd like to have a Top 5 list which will list, in the correct order, the most frequently occurring text strings, like this:
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]
C
[/TD][TD]
D
[/TD][/TR]
[TR]
[TD]1[/TD]
[TD]Top 5[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Germany[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]United Kingdom[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Italy[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]United States of America[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]France[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]
Can anyone suggest a formula which would do the job? Nothing I've found via Google seems to do the job.
Much obliged,
Paul