-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Find most frequently occurring text string in a VISIBLE or filtered
- Thread starter xdenama
- Start date
To return the most frequently occurring text value in visible or filtered rows, let's say B2:B10, try the following array formula that needs to confirmed with CONTROL+SHIFT+ENTER.. Change the range references accordingly.
Also, to include in the calculations rows that have also been manually hidden, as apposed to filtered, change the number 3 in SUBTOTAL to 103.
Unfiltered Data
The formula returns Y.
Filtered Data (filtered for A)
Formula returns X.
Hope this helps!
VBA Code:
=INDEX(B2:B10,MODE(IF(SUBTOTAL(3,OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,1))>0,MATCH(B2:B10,B2:B10,0))))Also, to include in the calculations rows that have also been manually hidden, as apposed to filtered, change the number 3 in SUBTOTAL to 103.
Unfiltered Data
| Name | Category |
| A | Z |
| B | Y |
| C | Y |
| A | X |
| A | X |
| B | Y |
| C | Y |
| B | Y |
| A | X |
The formula returns Y.
Filtered Data (filtered for A)
| Name | Category |
| A | Z |
| A | X |
| A | X |
| A | X |
Formula returns X.
Hope this helps!
Upvote
0
Upvote
0
Similar threads
- Solved
