Find Top # Most Occuring Text String on Specified Filtered Column?

W

windywidjaya

New Member
Joined
Jan 21, 2018
Messages
3
Hi I wonder you guys can help me out of this case,

I want to rank the strings (1st-5th), in every city for example like this:
[TABLE="width: 0"]
<tbody>[TR]
[TD]City[/TD]
[TD]Fruits[/TD]
[TD][/TD]
[TD]Top Fruits in [/TD]
[TD]Washington[/TD]
[TD][/TD]
[TD]Top Fruits in [/TD]
[TD]Oakland[/TD]
[TD][/TD]
[TD]Top Fruits in [/TD]
[TD]San Diego[/TD]
[/TR]
[TR]
[TD]Washington[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]Cherry[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Washington[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]Cherry[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Washington[/TD]
[TD]Cherry[/TD]
[TD][/TD]
[TD]Cherry[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Washington[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Washington[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Washington[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Washington[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Washington[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Washington[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Washington[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oakland[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oakland[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oakland[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oakland[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oakland[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oakland[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oakland[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oakland[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oakland[/TD]
[TD]Cherry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oakland[/TD]
[TD]Cherry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oakland[/TD]
[TD]Cherry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oakland[/TD]
[TD]Cherry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]San Diego[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]San Diego[/TD]
[TD]Cherry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]San Diego[/TD]
[TD]Cherry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]San Diego[/TD]
[TD]Cherry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]San Diego[/TD]
[TD]Cherry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]San Diego[/TD]
[TD]Cherry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]San Diego[/TD]
[TD]Cherry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]San Diego[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I just learnt from another thread how to rank them but without the cities filter using this: (Array)
Code: 
[COLOR=#000000][SIZE=2][FONT=fixedsys]1st =INDEX($B:$B,MODE(IF(($B:$B<>""),MATCH($B:$B,$B:$B,0))))
2nd =INDEX($B:$B,MODE(IF(($B:$B<>"")*($B:$B<>[1st cell]),MATCH($B:$B,$B:$B,0))))
[/FONT][/SIZE][/COLOR][LEFT][COLOR=#000000][SIZE=2][FONT=fixedsys]3rd =INDEX($B:$B,MODE(IF(($B:$B<>"")*($B:$B<>[1st cell])*($B:$B<>[2nd cell]),MATCH($B:$B,$B:$B,0))))[/FONT][/SIZE][/COLOR][/LEFT]
[FONT=arial][COLOR=#000000][SIZE=2]
... and so on,[/SIZE][/COLOR][/FONT]

but how if I add the cities so that I can get result like the 3 tables on the right?

Looking forward to hear from you soon,
Thanks in Advance,
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Welcome to the forum.

It seems that a PivotTable would be much easier to construct and use than formulas for this type of task. Would a PT be acceptable to you?
 
Last edited:
Upvote 0
DRSteele said:
Welcome to the forum.

It seems that a PivotTable would be much easier to construct and use than formulas for this type of task. Would a PT be acceptable to you?
Click to expand...
Hi,

I couldn't agree more that Pivot Table is way much more easier to handle this type of task.
But my concern is that the source will be continuously add new data in row below from time to time.
And as I know that PT will cover only the data that available at the time it created, so when new data comes it doesn't calculate/refresh automatically.

Any ideas what should I use? Really appreciate your response :)
 
Upvote 0
There are 2 ways to Always be sure your Data Source area changes with your additions/deletions.
1) Old Method: Assign a NameRange using a Dynamic RangeNaming Technque.
2) New Method: Assign your Data Source as an Excel Table (Assigning a TableName).
 
Upvote 0
Assuming data starting from Row 2.
E2 cell to contain City to look up

In D3 enter:

=INDEX($B:$B,MODE(IF(($B:$B<>"")*($B:$B<>D7)*($B:$B<>D8)*(A:A=$E$2),MATCH($B:$B,$B:$B,0)),IF(($B:$B<>"")*($B:$B<>D7)*($B:$B<>D8)*(A:A=$E$2),MATCH($B:$B,$B:$B,0))))

Array enter CTRL+SHIFT+ENTER

Copy to cells below.

In E3 enter
=COUNT(IF((B:B=D3)*(A:A=$E$2),1))

Array enter CTRL+SHIFT+ENTER

Copy to cells below
 
Upvote 0
Try this code for results on Sheet2:-
NB:- Make sure you have a "Sheet2"
Code: 
[COLOR="Navy"]Sub[/COLOR] MG01Feb21
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Ray()
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Lg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] p [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] r [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
 [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
                Dic(Dn.Value).Add (Dn.Offset(, 1).Value), 1
            [COLOR="Navy"]Else[/COLOR]
                Dic(Dn.Value).Item(Dn.Offset(, 1).Value) = Dic(Dn.Value).Item(Dn.Offset(, 1).Value) + 1
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
ReDim nRay(1 To Rng.Count, 1 To Dic.Count * 2)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
        c = 1: r = 0: nStr = ""
        Ac = Ac + 2
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(k)
                ReDim Preserve Ray(r)
                Ray(r) = Dic(k).Item(p)
                r = r + 1
            [COLOR="Navy"]Next[/COLOR] p
    [COLOR="Navy"]For[/COLOR] Num = 0 To UBound(Ray)
        Lg = Application.Large(Ray, Num + 1)
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(k)
           [COLOR="Navy"]If[/COLOR] Not IsEmpty(Ray(Num)) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]If[/COLOR] Dic(k).Item(p) = Lg [COLOR="Navy"]Then[/COLOR]
                        c = c + 1
                        nRay(1, Ac - 1) = "Top Fruits in": nRay(1, Ac) = k
                        nRay(c, Ac - 1) = p: nRay(c, Ac) = Dic(k).Item(p)
                        [COLOR="Navy"]Exit[/COLOR] For
                [COLOR="Navy"]End[/COLOR] If
           [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] p
    [COLOR="Navy"]Next[/COLOR] Num
Erase Ray
[COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(6, UBound(nRay, 2))
    .Value = nRay
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.
On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
Sheet2 should now be updated.

Regards Mick




Regards Mick
 
Upvote 0
In D3 enter:

=INDEX($B:$B,MODE(IF(($B:$B<>"")*($B:$B<>​
​
D7​
​
)*($B:$B<>​
​
D8​
​
)*(A:A=$E$2),MATCH($B:$B,$B:$B,0)),IF(($B:$B<>"")*($B:$B<>​
​
D7​
​
)*($B:$B<>​
​
D8​
​
)*(A:A=$E$2),MATCH($B:$B,$B:$B,0))))

When RED-HIGHLITED cells get copied down - things seem to go awry...
​
​
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Index/Match - Return value if partial text from cell contained in separate column
2
Replies
16
Views
4K
Kevin_Li
Kevin_Li
N
  • Question Question
Most frequently occurring visible text string in a filtered column
Replies
9
Views
491
ninjapaping
ninjapaping
An Quala
  • Question Question
Delete the entire row if it's column does NOT have specific value
Replies
1
Views
748
An Quala
An Quala
Unlucky
  • Question Question
MOST COMMON TEXT
Replies
3
Views
607
Unlucky
Unlucky
M
  • Question Question
Returning the top 3 occuring text strings in a range
Replies
3
Views
1K
jasonb75
J

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top