Counting different values in high frequency function
I was tasked with counting the number of distinct rows in an excel column. A quick Google search later gave the following formula, here :
= SUM (IF (FREQUENCY (MATCH (B2: B10, B2: B10,0), MATCH (B2: B10, B2: B10,0))> 0.1))
Consider the data:
a B C D B E C
Now the match function will return an array (since the first argument is an array):
1 2 3 4 1 2 7 3
So far so good. I don't understand how the FREQUENCY function works, in particular how it handles repositories that are being replicated (e.g. bit 1 is replicated in the above data). The result of the frequency function is:
2 2 2 1 0 0 1 0 0
thank
Taras
source to share
EDIT : I figured out how your solution works, as amended to reflect this.
FREQUENCY searches the search array for entries from your baskets. This is how it works:
Search for an array: 1 2 3 4 1 2 7 3
Bins: 1 2 3 4 1 2 7 3
Bin 1 => there are two 1 => 2
Bin 2 => there are two 2 => 2
Bin 3 => there are two 3 => 2
Bin 4 => there is one 4 => 1
Bin 1 repeat => 1 already counted => 0
Bin 2 repeat => 2 already counted => 0
Bin 7 => there is one 7 => 1
Bin 3 repeat => 3 already counted => 0
It looks like the solution uses the FREQUENCY quirk, that is, it won't count the same bit twice, because you can expect the second bit with a value of 1 to be nonzero. But the way it works - since it will only count the number of occurrences for the first bin and not a duplicate, the number of rows with a value greater than zero will give you the number of distinct entries.
Here's an alternative approach that you might find useful. it can be used to calculate the number of distinct values:
Suppose your row range is B2: B10. Fill in another column
=(MATCH(B2,B$2:B2,1)-(ROW(B2)-ROW(B$2)))>0
The line should change when copied, so the second line should be like:
=(MATCH(B3,B$2:B3,1)-(ROW(B3)-ROW(B$2)))>0
This signals TRUE if the current line contains the first instance of the line (if you give it a couple of minutes, you should be able to determine what it does). So if you count the number of TRUEs with COUNTIF (), you should get the number of distinct rows.
source to share
You can use the vba procedure:
Sub Uniques()
Dim rng As Range
Dim c As Range
Dim clnUnique As New Collection
Set rng = Range("A1:A8")
On Error Resume Next
For Each c In rng
clnUnique.Add c.Value, CStr(c.Value)
Next c
On Error GoTo 0
MsgBox "Number of unique values = " & clnUnique.Count
End Sub
If you need to display unique results, you can simply browse the collection and write the values in your worksheet.
source to share