Count occurances of numbers in a particular range in a single cell

**dekoay** · 05-10-2012, 08:38 PM

Hi, I have this problem I can't solve.

I have some data I need to sort out. It's presented this format:

045 385 195 375 193 586 395 183 659 395 (all in a single cell. There's actually over a hundred 3-digit numbers but I just pasted 10 here)

I need to count the occurrences of 3-digit numbers under 300. Please help!!

(btw I can't do it manually, as I have hundreds of such cells to count!)

thanks!

**JBeaucaire** · 05-10-2012, 09:09 PM

This is easier to test if you posted a workbook with some actual data and sample "results". If understand this:

1) A cell (A1) has a string of 300 3-digit codes separate by spaces (no returns, line feeds, just spaces)
2) You want a count (a single number) of all the number lower than 300.

So, in the example:

045 385 195 375 193 586 395 183 659 395

... the answer would simply be: 4

Here's a UDF you can put in a standard code module (Insert > Module)

Option Explicit

Function ARRCOUNT(MyRNG As Range, Threshold As Long, Optional Delim As String) As Long
Dim cell As Range, MyARR As Variant, v As Long

If Delim = "" Then Delim = " "

For Each cell In MyRNG
    MyARR = Split(cell, Delim)
    For v = LBound(MyARR) To UBound(MyARR)
        If MyARR(v) < Threshold Then ARRCOUNT = ARRCOUNT + 1
    Next v
Next cell

End Function

That is then used in another cell to evaluate one or more cells. The function is used like so:

=ARRCOUNT(RangeOfCells, Threshold, Optional Delimiter)

For example:
=ARRCOUNT(A1, 300, " ")

...or this, which omits the delimiter causing the function to default to spaces:
=ARRCOUNT(A2:A12, 100)

http://screencast.com/t/UOx6Agg2QD

**dekoay** · 05-10-2012, 09:29 PM

Thanks for your replies! Sorry this is a noob question, but where do I put in standard code modules?

**StevenM** · 05-10-2012, 09:20 PM

The function Count3DigitNumbers counts each three digit number in a string separated by spaces.
It returns a string with the occurrence of each number.
Thus: "045 385 195 045 375 193 193 586 395 183 659 395"
Becomes: "045:2;183:1;193:2;195:1"
045 occurred 2x; 183 once; 193 2x; 195 once.
Run: Run_Count3DigitNumbers for an example.

Function Count3DigitNumbers(str As String) As String
    Dim nArray(0 To 300) As Long, i As Long, nItem As Long
    Dim sArray() As String
    sArray = Split(str, " ")
    For i = LBound(sArray) To UBound(sArray)
        If IsNumeric(sArray(i)) Then
            nItem = CLng(Val(sArray(i)))
            If nItem >= 0 And nItem < 300 Then
                nArray(nItem) = nArray(nItem) + 1
            End If
        End If
    Next i
    str = ""
    For i = LBound(nArray) To UBound(nArray)
        If nArray(i) > 0 Then
            str = str & Format(i, "000") & ":" & nArray(i) & ";"
        End If
    Next i
    If Len(str) Then str = Left$(str, Len(str) - 1)
    Count3DigitNumbers = str
End Function

Sub Run_Count3DigitNumbers()
    Dim sList As String
    sList = Count3DigitNumbers("045 385 195 045 375 193 193 586 395 183 659 395")
    sList = Replace(sList, ";", vbCr)
    MsgBox sList
End Sub

**StevenM** · 05-10-2012, 09:36 PM

(1) One needs a Microsoft Office Excel Macro-Enabled Worksheet (.xlsm). You can create one by saving an excel file "Save as" Macro-Enabled Worksheet. (Your Example Worksheet appeared to be an Excel Macro-Enabled Worksheet.)

(2) Your Macro-Enabled Worksheet needs to be in a folder which you can designate was macro friendly. The way you do this: click on the Office Button -> Excel Options -> Trust Center -> Trust Center Setting -> Trusted Locations. Then you add your folder (where you have your Excel Macro-Enabled Worksheet) as a trusted location.

(3) With your Excel Macro-Enabled Worksheet go to the menu item "Developer" -> Visual Basic.

(4) In the VBE (Visual Basic Editor), go to Tools -> Options and click the "Editor" tab and check the box next to "Require Variable Declarations" (if not checked). (This is not necessary, but good practice. Only needs to be done once.)

(5) From the menu: Insert -> Module; a module should open to the right with "Option Explicit" (in blue type).

(6) Copy and paste any code under "Option Explicit":

If you get stuck on a step, just ask.