Hello,
So, I have a list of data. For these purposes my data is all text strings.
Is there a way I can use a formula to return the top 3 most frequent strings in a column, while also using an IF statement (or similar) to include only a subset of the overall dataset?
I'll use an example:
I know that I can use formulae to return the most frequent strings from the category column. I've used a formula found online (as it happens I know the formula works but can't claim to fully understand how it works):
Using
=OFFSET($A$1,MODE(MATCH($A$2:$A$40,$A$2:$A$40,0)),0)
In an initial cell, then
=OFFSET($A$1,MODE(IF(COUNTIF($D$1:$D1,$A$2:$A$40)=0,MATCH($A$2:$A$40,$A$2:$A$40,0)+{0,0})),0)
and
=OFFSET($A$1,MODE(IF(COUNTIF($D$1:$D2,$A$2:$A$40)=0,MATCH($A$2:$A$40,$A$2:$A$40,0)+{0,0})),0)
etc
[I would be interested to know if anyone can actually explain to me what this formula is doing?]
I can't find a way to use a formula / nest a function into this formula that would then only return the most frequent entries that also are only in Filter B, or Filter A, for example.
I've tried to upload a mini-sheet but not sure if it'll work.
Sincerely
EffDeeArr
So, I have a list of data. For these purposes my data is all text strings.
Is there a way I can use a formula to return the top 3 most frequent strings in a column, while also using an IF statement (or similar) to include only a subset of the overall dataset?
I'll use an example:
Category | Filter |
Item 1 | B |
Item 1 | B |
Item 1 | B |
Item 2 | A |
Item 2 | B |
Item 4 | A |
Item 4 | A |
Item 4 | B |
Item 4 | B |
I know that I can use formulae to return the most frequent strings from the category column. I've used a formula found online (as it happens I know the formula works but can't claim to fully understand how it works):
Using
=OFFSET($A$1,MODE(MATCH($A$2:$A$40,$A$2:$A$40,0)),0)
In an initial cell, then
=OFFSET($A$1,MODE(IF(COUNTIF($D$1:$D1,$A$2:$A$40)=0,MATCH($A$2:$A$40,$A$2:$A$40,0)+{0,0})),0)
and
=OFFSET($A$1,MODE(IF(COUNTIF($D$1:$D2,$A$2:$A$40)=0,MATCH($A$2:$A$40,$A$2:$A$40,0)+{0,0})),0)
etc
[I would be interested to know if anyone can actually explain to me what this formula is doing?]
I can't find a way to use a formula / nest a function into this formula that would then only return the most frequent entries that also are only in Filter B, or Filter A, for example.
I've tried to upload a mini-sheet but not sure if it'll work.
Sincerely
EffDeeArr
Book4.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | String | Filter | Telephone2 | 11 | |||
2 | Telephone1 | 1 | Telephone4 | 11 | |||
3 | Telephone1 | 2 | Telephone3 | 5 | |||
4 | Telephone1 | 2 | Telephone1 | 3 | |||
5 | Telephone10 | 1 | Telephone10 | 1 | |||
6 | Telephone11 | 1 | Telephone11 | 1 | |||
7 | Telephone12 | 1 | Telephone12 | 1 | |||
8 | Telephone13 | 1 | |||||
9 | Telephone2 | 1 | |||||
10 | Telephone2 | 1 | |||||
11 | Telephone2 | 1 | |||||
12 | Telephone2 | 1 | |||||
13 | Telephone2 | 1 | |||||
14 | Telephone2 | 1 | |||||
15 | Telephone2 | 1 | |||||
16 | Telephone2 | 1 | |||||
17 | Telephone2 | 1 | |||||
18 | Telephone2 | 1 | |||||
19 | Telephone2 | 1 | |||||
20 | Telephone3 | 1 | |||||
21 | Telephone3 | 1 | |||||
22 | Telephone3 | 1 | |||||
23 | Telephone3 | 1 | |||||
24 | Telephone3 | 1 | |||||
25 | Telephone4 | 1 | |||||
26 | Telephone4 | 1 | |||||
27 | Telephone4 | 1 | |||||
28 | Telephone4 | 1 | |||||
29 | Telephone4 | 1 | |||||
30 | Telephone4 | 1 | |||||
31 | Telephone4 | 1 | |||||
32 | Telephone4 | 1 | |||||
33 | Telephone4 | 1 | |||||
34 | Telephone4 | 1 | |||||
35 | Telephone4 | 1 | |||||
36 | Telephone5 | 1 | |||||
37 | Telephone6 | 1 | |||||
38 | Telephone7 | 1 | |||||
39 | Telephone8 | 1 | |||||
40 | Telephone9 | 1 | |||||
41 | |||||||
42 | |||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D1 | D1 | =OFFSET($A$1,MODE(MATCH($A$2:$A$40,$A$2:$A$40,0)),0) |
E1:E7 | E1 | =COUNTIFS(A:A,D1) |
D2:D7 | D2 | =OFFSET($A$1,MODE(IF(COUNTIF($D$1:$D1,$A$2:$A$40)=0,MATCH($A$2:$A$40,$A$2:$A$40,0)+{0,0})),0) |
Press CTRL+SHIFT+ENTER to enter array formulas. |