Filtered Rows using Mode Function?

[QTE]

Hi Excel Forum,

I am using numeric "filtered" data and I need to find the most frequen
/ re-occurring values (1st, 2nd, 3rd, 4th, 5th etc.) from the visibl
filtered rows. The Mode function seemed likely, but I cannot get it t
work with filtered rows.

Can you assist with working examples, please:

Formula based input direct on worksheet.
VBA Macro using Formula.
VBA UserDefined Function.

Thank you
QT

 

[BrianB]

I would use one of 2 methods :-

1. Formula in another column and sort descending :-
=COUNTIF($A$1:$A$20,A16)
Need to sort out duplicates.

2. A pivot table set to *Count* the number of occurrences of your dat
and sort descending. These also have a "Top x" feature. No duplicates

 

[QTE]

Hi Brian,

Thanks for solutions. I'm not quite up to speed with Excel (not by
long chalk) and my logic at times is suspect!

*I would use one of 2 methods :-

1. Formula in another column and sort descending :-
=COUNTIF($A$1:$A$20,A16)
Need to sort out duplicates.
*

Regarding your first solution: would you be so kind as to explain th
relevance of sorting in descending order and the need to sort ou
duplicates - is this the frequency duplicates or data duplicates?

What will sorting by descending order achieve?
How do I go about sorting out the duplicates?

I thought I needed the data duplicates to be counted to give the mos
frequently occurring value(s) in the range of visible filtered cells.
See, I've confused myself without even trying.

Help!

Kind regards
QT

 

[BrianB]

What will sorting by descending order achieve?
You get the ones most occurring at the top.

How do I go about sorting out the duplicates?

Visually (by looking) ? Or Data/Filter/Autofilter ?

The *formula* method does not remove any rows, so each occurrence of
value will have the same number (count) against it. If you have
occurrences of a value you will have 5 rows with number 5 - al
together after sorting.

The *pivot table* method will just show the value and the number o
times it occurs

 

[QTE]

Hi Brian,

Thanks for clarification.


Regards,
QT