calculate the most common text values by groups

[alaas]

Hello!

I want to calculate the most common text value for groups. I have a table with a lot of records like an example below.

ID Code
1 HB
1 KU
1 KS
1 LV
1 KS
2 LM
2 KS
2 KS
2 LV
2 KS
3 KS
3 KS
3 KS
3 KS
3 KU
3 KS
3 LM
3 LV
3 KS

I want a table like below:
ID Code
1 KU
2 KS
3 KS

How can I do it in Excel 2010?

Thank you in advance.
Anne

 

[MickG]

Try this:-
Your data in sheet1, columns "A & B".
Results start "C1"

[COLOR="Navy"]Sub[/COLOR] MG14Mar23
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic1        [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Dic2        [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] k
[COLOR="Navy"]Dim[/COLOR] Num         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
[COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Set[/COLOR] Dic2 = CreateObject("scripting.dictionary")
        Dic2.CompareMode = vbTextCompare
[COLOR="Navy"]Set[/COLOR] Dic1 = CreateObject("scripting.dictionary")
        Dic1.CompareMode = vbTextCompare
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Temp = Dn.Offset(, 1)
    [COLOR="Navy"]If[/COLOR] Not Dic1.Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Set[/COLOR] Dic2 = CreateObject("scripting.dictionary")
            Dic2.CompareMode = vbTextCompare
            Dic2.Add Temp, 1 '[COLOR="Green"][B]Dn[/B][/COLOR]
            Dic1.Add Dn.Value, Dic2
    [COLOR="Navy"]Else[/COLOR]
       [COLOR="Navy"]If[/COLOR] Not Dic1(Dn.Value).Exists(Temp) [COLOR="Navy"]Then[/COLOR]
              Dic1(Dn.Value).Add (Temp), 1 '[COLOR="Green"][B]Dn[/B][/COLOR]
        [COLOR="Navy"]Else[/COLOR]
               Dic1(Dn.Value).Item(Temp) = Dic1(Dn.Value).Item(Temp) + 1
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Dim[/COLOR] g
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ntemp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
ReDim Ray(1 To Dic1.Count, 1 To 2)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic1.keys
    c = c + 1
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] g [COLOR="Navy"]In[/COLOR] Dic1.Item(k)
        [COLOR="Navy"]If[/COLOR] Dic1.Item(k)(g) > ntemp [COLOR="Navy"]Then[/COLOR]
            Ray(c, 1) = k
            Ray(c, 2) = g
            Num = Dic1.Item(k)(g)
        [COLOR="Navy"]End[/COLOR] If
            ntemp = Dic1.Item(k)(g)
    [COLOR="Navy"]Next[/COLOR] g
 [COLOR="Navy"]Next[/COLOR] k
Range("C1").Resize(Dic1.Count, 2) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

If your unsure how to run the code call back !!!
Regards Mick

 

[PaddyD]

or use a pivot table.

 

[alaas]

Thank you for the code. I have never used Excel codes. I looked for help how to use codes in Excel but I didn't succeed in running your code. I just don't know how to run it.

Anne

 

[alaas]

I would like to use the pivot table, but I do not know how can I find the most common text value with pivot table. There seems to be no suitable statistics for that.

Anne

 

[MickG]

To run the code:-
Make sure you data is in Sheet1, or alter this line to suit "With Sheets("Sheet1")".
Right click the TAB of this Data sheet.
From the drop down menu click "View Code"
VB Window appears.
From the vbwindow Toolbar select "Insert", then "Module", New vbWindow appears.
Paste the code into this window.
Close VBWindow.

To run the code:-
Click "Alt + F8", Macro dialog Box appears, Select the name "MG14Mar23" from the list, Click run from the right hand side of the Dialog Box.
Column "C" of the data sheet should now show the result.

 

[PaddyD]

what version of excel are you on?

if it's 2007:

insert | pivot table...step through the wizard, drag ID into the row area, then code into the row area, then code again into the data area. Will give you count of code by ID. To limit to most frequent items - once the pivot table is on the sheet, select the little drop down arrow on the row labels. make sure that code is selected, then go to value filter. select top ten and limit it to show top one.

 

[alaas]

PaddyD

Re: calculate the most common text values by groups
what version of excel are you on?

if it's 2007:

insert | pivot table...step through the wizard, drag ID into the row area, then code into the row area, then code again into the data area. Will give you count of code by ID. To limit to most frequent items - once the pivot table is on the sheet, select the little drop down arrow on the row labels. make sure that code is selected, then go to value filter. select top ten and limit it to show top one.


​

I use Excel 2010. There is no such option like top ten. The only option that works with text data is count, but it counts the the codes from whole table, not based on groups.

 

[alaas]

To run the code:-
Make sure you data is in Sheet1, or alter this line to suit "With Sheets("Sheet1")".
Right click the TAB of this Data sheet.
From the drop down menu click "View Code"
<acronym title="vBulletin">VB</acronym> Window appears.
From the vbwindow Toolbar select "Insert", then "Module", New vbWindow appears.
Paste the code into this window.
Close VBWindow.

To run the code:-
Click "Alt + F8", Macro dialog Box appears, Select the name "MG14Mar23" from the list, Click run from the right hand side of the Dialog Box.
Column "C" of the data sheet should now show the result.​

Anne:
The code works well. Thank you very much!

 

[PaddyD]

Yes there is

This may help make it clearer:

Excel Pivot Table Filters - Top 10