Frequency value of filtered items

[azizrasul]

When you use filtering in MS Excel, is there a way that would give the number of occurences of each filtered value?

 

[Firefly2012]

Hi

Assuming you mean via VBA then yes - you could iterate thru the visible cells (ie filtered results) in your column of interest and store the count of each unique value. A Dictionary object is particularly suited to this task. Is this something you would like to explore?

 

[Robert Mika]

When you use filtering in MS Excel, is there a way that would give the number of occurences of each filtered value?

Like this?:

No	Letter
1	a
2	b
1	b
2	d
1	c
2	b
1	a
2	c

<TBODY>
</TBODY>

Filter by 2 in column A to get:

No	Letter
2	b
2	d
2	b
2	c

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

E1:b
F1:d
G1:c

In E2 copy accros:
=SUMPRODUCT(SUBTOTAL(2,OFFSET($A$2:A$10,ROW($A$2:$A$10)-MIN(ROW($A$2:$A$10)),,1)),--($B$2:$B$10=E1))

 

[azizrasul]

When you filter on a particular header say First Name, you could get (when you filter) something like

Jones
Smith

What I'm looking for is

Jones 5
Smith 8

where there 5 occurences of Jones and 8 occurences of Smith. Either a solution using VBA or if there is a facility within Excel that already this that would also be good.

 

[Robert Mika]

If you got 5 Jones's in column and you filter by this Name the result will not change

What am I missing?

 

[azizrasul]

When you filter on the column, you will only see Jones and Smith but there is no way of knowing how many Jones's and Smith's there are. Imagine I have 100,000 records with say 250 different surnames. How would I know what the breakup of each surname is by just filtering. The filter will simply give me the unique surnames not the number of thimes they occur. Hope that's clearer.