Excel counting most common values

[Legacy 397974]

Hello, I am struggling to create a formula, which will count the most frequent values from column A, based on the values from column B. For instance, I would like to check the top 3 (most frequent ones) text values in column A, but only for the ones which have value equal to 1 in column B, same would go for the values in col A, but with 2, 3 in col B etc. I'd appreciate some suggestions. Many thanks.

 

[Aladin Akyurek]

The values in column B are not frequencies, right?

 

[Legacy 397974]

The values in column B are not frequencies, right?

The values in col B are unique categories from 1 to 5, those are not frequencies.

 

[istiasztalos]

Hi Jojyina,

With a pivot table this would be very easy, you can filter for B, place A in rows and sort the results. Is that an option for you?

If for some reason a pivot table is not an option, a helper table would be very beneficial, you can use formulas to generate a list of unique items from A and count them where B meets the criteria. This table could be easily referenced later where you need the final output.

If you need a one cell no VBA formula based solution, it may be a bit tricky (unless Aladin proposes some really cool formula combination )

This can be used to generate an array of frequencies, 'list' is column A, 'critlist' is column B, 'crit' is the criteria
MMULT(TRANSPOSE(ROW(INDIRECT("A1:A"&ROWS(list))))^0,IF(IF(critlist=crit,list)=TRANSPOSE(list),1,0))

And it could be combined with an INDEX MATCH to check in itself where is it's maximum value and return value from column A in the same position, that would be the most frequent one. For the second and third most frequent the same can be used again with the modification to also exclude the previous results

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td]item[/td][td]code[/td][td]

1​

[/td][td]

2​

[/td][/tr]
[tr][td]

2​

[/td][td]jad[/td][td]

2​

[/td][td]most freq items[/td][td][/td][/tr]
[tr][td]

3​

[/td][td]kad[/td][td]

2​

[/td][td="bgcolor:#DAEEF3"]lad[/td][td="bgcolor:#DAEEF3"]lad[/td][/tr]
[tr][td]

4​

[/td][td]lad[/td][td]

1​

[/td][td="bgcolor:#DAEEF3"]kad[/td][td="bgcolor:#DAEEF3"]jad[/td][/tr]
[tr][td]

5​

[/td][td]lad[/td][td]

2​

[/td][td="bgcolor:#DAEEF3"]vad[/td][td="bgcolor:#DAEEF3"]kad[/td][/tr]
[tr][td]

6​

[/td][td]kad[/td][td]

1​

[/td][td="bgcolor:#DAEEF3"][/td][td="bgcolor:#DAEEF3"]nad[/td][/tr]
[tr][td]

7​

[/td][td]kad[/td][td]

3​

[/td][td="bgcolor:#DAEEF3"][/td][td="bgcolor:#DAEEF3"][/td][/tr]
[tr][td]

8​

[/td][td]lad[/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]lad[/td][td]

2​

[/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]lad[/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]lad[/td][td]

3​

[/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]kad[/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]lad[/td][td]

3​

[/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td]nad[/td][td]

2​

[/td][td][/td][td][/td][/tr]
[tr][td]

15​

[/td][td]vad[/td][td]

1​

[/td][td][/td][td][/td][/tr]
[/table]

1. Define Item in the Name Manager as referring to the range A2:A15.

2. Define Code in the Name Manager as referring to the range B2:B15.

3. Define Ivec in the Name Manager as referring to:

=ROW(Item)-ROW(INDEX(Item,1,1))+1

4. In C3 control+shift+enter, not just enter, copy across, and down:

=IF(ROWS(C$3:C3)>SUM(IF(LARGE(FREQUENCY(IF(1-(Item=""),IF(Code=C$1,MATCH(Item,Item,0))),Ivec),Ivec)>=LARGE(FREQUENCY(IF(1-(Item=""),IF(Code=C$1,MATCH(Item,Item,0))),Ivec),3),1)),"",INDEX(Item,SMALL(IF(FREQUENCY(IF(1-(Item=""),IF(Code=C$1,MATCH(Item,Item,0))),Ivec)=LARGE(FREQUENCY(IF(1-(Item=""),IF(Code=C$1,MATCH(Item,Item,0))),Ivec),ROWS(C$3:C3)),Ivec),SUM(IF(LARGE(FREQUENCY(IF(1-(Item=""),IF(Code=C$1,MATCH(Item,Item,0))),Ivec),ROW(C$3:C3)-ROW(C$3)+1)=LARGE(FREQUENCY(IF(1-(Item=""),IF(Code=C$1,MATCH(Item,Item,0))),Ivec),ROWS(C$3:C3)),1)))))

Note 1. The foregoing formula makes it clear that we need Longre's SETV/GETV dearly as native functions in Excel.

Note 2. We can use Stephen Dunn's V() a work-alike of Longre's pair in order to avoid computing the same thing multiple times within the formula.

Using V(), the formula in [4] becomes:

=IF(ROWS(C$3:C3)>SUM(IF(LARGE(V(FREQUENCY(IF(1-(Item=""),IF(Code=C$1,MATCH(Item,Item,0))),Ivec)),Ivec)>=LARGE(V(),3),1)),"",INDEX(Item,SMALL(IF(V()=LARGE(V(),ROWS(C$3:C3)),Ivec),SUM(IF(LARGE(V(),ROW(C$3:C3)-ROW(C$3)+1)=LARGE(V(),ROWS(C$3:C3)),1)))))

For the foregoing formula to work, we need to install the V() function using Alt+F11 of which the code is as follows:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

 

[Legacy 397974]

Thank you so much Aladin, that's precisely the formula I needed for my file. That's really great solution.

 

[Aladin Akyurek]

Thank you so much Aladin, that's precisely the formula I needed for my file. That's really great solution.

You are welcome. Thanks for the kind update.