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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
3K
Kevin_Li
Kevin_Li
N
  • Question Question
Most frequently occurring visible text string in a filtered column
Replies
9
Views
432
ninjapaping
ninjapaping
An Quala
  • Question Question
Delete the entire row if it's column does NOT have specific value
Replies
1
Views
742
An Quala
An Quala
Unlucky
  • Question Question
MOST COMMON TEXT
Replies
3
Views
602
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,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

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