Solved: Counting account numbers

[Blackie50]

Hi there

I have a sheet which has multiple occurrences of account numbers and I need to be able to count how many unique account numbers there are. The sheet varies in size as entries are created and deleted. E.G.

1234561P3
1526330P2
2566323P0
1234561P3 appears already
4122545P2
1234561P3 appears already
4122545P2 appears already

So the count would be 4 (note the 8th character is always P - although doubt this makes a difference)

Any help greatly appreciated
thanks
Jon

[mohanvijay]

you can get unique account numbers manually by using advanced filter

if you want the VBA to do that try this

 
Dim OO_Dic As Object
Dim Col_ID As Integer
Dim ST_RW As Long, Last_RW As Long
Dim i As Long
Dim Ac_No As String
Set OO_Dic = CreateObject("Scripting.Dictionary")
Col_ID = 1  ' set the column of account numbers
ST_RW = 2  'starting row no
Last_RW = Cells(Rows.Count, Col_ID).End(xlUp).Row  'get last row no
For i = ST_RW To Last_RW
    Ac_No = Trim(Cells(i, Col_ID).Value)
    
    If Ac_No <> "" Then
    
        If OO_Dic.exists(Ac_No) = False Then
            OO_Dic.Add Ac_No, Ac_No
        End If
    End If
Next i
MsgBox OO_Dic.Count 'Unique count
Set OO_Dic = Nothing

[Bob Phillips]

For a count, use

=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))

[Blackie50]

Thanks very much for replies - both work fine

regards
Jon

[GTO]

For a count, use

=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))

@xld:

For us mere mortals, what does appending the last range reference with an empty string do? More accurately, how? I split the formula and see that part of the array returned (for empty cells) returns a #DIV/0! error without the appending.

Thank you so much,

Mark

[Bob Phillips]

Here's a synopsis of the whole formula.

Let's start by defining the range A1:A20 to talk specifics.

Bob,John,Bob,Bob,John,John,Bob,Bill,Bill,Max

or data in just A1:A10

The basic formula to count unique items is

=SUMPRODUCT(1/COUNTIF($A$1:$A$10,$A$1:$A$10))

The COUNTIF($A$1:$A$10,$A$1:$A$10) part of the formula builds an array of the number of occurrences of each item, in this case{4;3;4;4;3;3;4;2;2;1}. As can be seen, each occurrence of the repeated value is counted, so there are four occurrences of Bob in the array. There will always be the same number of occurrences of value as the count of that value, unless two or more items are repeated the same number of times, in which case it will be some multiple of that count.

Thus the item that is repeated 4 times has 4 instances of that count, dividing 1 by the count of 4, gives 0.25 4 times. The full array of values is

{0.25;0.333333333333333;0.25;0.25;0.333333333333333;0.333333333333333;0.25; 0
.5;0.5;1}.

The item that repeats 4 times sums to 1. The item that repeats 3 times also sums to 1. It should be clear from this that every value works in the same way and sums to 1. In other words, 1 is returned for every unique item. The sum of these values becomes the count of unique items.

As our test range is A1:A20, and some of the items in A1:A20 are blank, extending this formula to A1:A20 would return a #DIV/0! Error.

The reason for the error is blank cells in the full range A1:A20. Each blank cell returns a 0 value from the COUNTIF formula, which gives the #DIV/0! Error when divided into 1.

The solution to this is to force it to count the empty cells as well, and not return a zero. Adding &"" to the end of the COUNTIF formula forces a count of the blanks.

This addition on its own removes the #DIV/0! error, but will cause the blanks to be counted as a unique item. A further addition to the formula resolves this by testing for those blanks. Instead of dividing the array of counts into 1 each time, adding the test creates an array of TRUE/FALSE values to be divided by the equivalent element in the counts array. Each blank will resolve to FALSE in the dividend array, and the count of the blanks in the divisor array. The result of this will be 0, so the blanks do not get counted.

[GTO]

Thank you Bob. While probably the simplest bit of the formula, this was where I was initially lost.

...what does appending the last range reference with an empty string do? More accurately, how?

I am afraid the 'how' was where I was just not getting it. It is funny how sometimes the simplest part of something will be the tripping point. I believe I am 'on track' now, after following your examples, re-testing the individual 'pieces', and for lack of better verbiage, getting 'unstuck' on COUNTIF. Let us see if that is true

Using your example range of A1:A20 (and example data in A1:A10) as a starting point:

If we extend the initial COUNTIF, entering =COUNTIF("A1:A20","A1:A20") in B1:B20 (all formulas confirmed via CSE unless noted) our return will be the array {4;3;4;4;3;3;4;2;2;1;0;0;0;0;0;0;0;0;0;0}.

Of interest here (and where my brain was probably going 'kathunk') is that in the eleventh thru twentieth elements, COUNTIF is returning zero counts, as it is reading the corresponding blank cell as a number (0), but in turn is looking for a string value of "0". Thus, a zero count is returned for each of these elements, as there are no cells explicitly containing 0.

To simplify further, if we enter in C1:C20 the formula =A1:A20, the first ten elements are the names, but the last ten are zero values, returned as numbers (naturally right-aligned), as the range's values are being read.

Now I hope I state this next part correctly:

If however, let us say in D1:D20, we enter the formula =A1:A20&"", the appending of each cell value with a vbNullstring coerces or forces the formula to read each cell's .Text property (to borrow from vba terminology), rather than the cell's implied value of zero for blank cells.

Thus, if for instance A1:A20 now contained {1;2;1;1;2;2;1;3;3;4;;;;;;;;;;}, then our formula in C1:C20 will return {1;2;1;1;2;2;1;3;3;4;0;0;0;0;0;0;0;0;0;0}, and these will all be returned as numbers due to Excel reading the value of each cell and finding nothing but numbers (including the implied zeros).

Our formula in D1:D20 however, will now return {1;2;1;1;2;2;1;3;3;4;;;;;;;;;;}, and the first ten elements will be returned as Strings (left-aligned), due to appending "" to each referenced cell. The last ten elements returned are now of course empty strings.

The solution to this is to force it to count the empty cells as well, and not return a zero. Adding &"" to the end of the COUNTIF formula forces a count of the blanks.

So we are forcing COUNTIF to read each cell's .Text property in essence, and then as you pointed out, returning {4;3;4;4;3;3;4;2;2;1;10;10;10;10;10;10;10;10;10;10}.

Sorry for the slow response and I thank you for your explanation. I wish MS would just supply an optional Bob button in place of the latest version of Help. Topics would certainly be more understandable.

Levity aside, it is a joy to read when you explain a thing.

Thanks again,

Mark