Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi YTD Summary | Excel Worksheet Functions | |||
Summary of data - multi sheets | Excel Worksheet Functions | |||
multi group with summary above with 1 overall summary line below | Excel Discussion (Misc queries) | |||
multi group with summary above with 1 overall summary line below | Excel Discussion (Misc queries) | |||
Extract values from a multi-select multi-column list-box | Excel Programming |