Summary of list with multi length numbers
 

Hi,

I have a list with numbers.

9411007816009306000
9411007816009306001
9411007816009306002
9411007816009306003
9411007816009306004
000123556790
000123556792
000123556796
000123556797
000123556794
000123556799
09459001890
09449001799
09469001790
09455001890
09456001890
-----------------------------------------
Result:

Head Count
816 4

0944 1
0945 3
0946 1


For any 19 digits I would like to count 816 using mid xl function.
For any 12 digits no count.
For any 11 digits I would like to count 0945, 0944, 0946 using left xl
function.



Thx.

Summary of list with multi length numbers
 

Hi Sinner

I have wrote some code that is one way to do what you want, though
again you are going to have troubles with your leading 0 as excel
automatically removes any leading zeros unless you are saving the
number as text??? Anywho I put an apostorphie in front of the numbers
beginning with a zero then instead of searching for the prefix 0944 I
used the mid function to look for 944.

The code will loop through all the used cells in column A using "For ...
Next" and check it's length using "Len" and check that against the
"Select Case" statements, if the "Len" is 19 check for the value you
want and if found iterate your counter by 1, or if the "Len" is 11
then it will run the nested "Select Case" statement to check for the
required values, if found iterate their counters.

Option Explicit
Dim c816, c944, c945, c946 As Integer
Dim c As Integer
Dim LastRow As Integer

Private Sub CommandButton1_Click()

c816=0
c944=0
c945=0
c946=0

LastRow = [A65336].End(xlUp).Row

For c = 1 To LastRow

Select Case Len(Cells(c, 1))

Case 19

If Mid(Cells(c, 1).Value, 9, 3) = 816 Then

c816 = c816 + 1

End If

Case 11

Select Case Mid(Cells(c, 1).Value, 2, 3)

Case 944

c944 = c944 + 1

Case 945

c945 = c945 + 1

Case 946

c946 = c946 + 1

End Select

End Select

Next

[b1].Value = 816
[c1].Value = c816
[b2].Value = "'0944"
[c2].Value = c944
[b3].Value = "'0945"
[c3].Value = c945
[b4].Value = "'0946"
[c4].Value = c946

End Sub


I hope this helps

Steve