Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting characters (2k)

    I have a column, in that column there are sequences of characters that i want to count. eg:

    Column A

    12,33,16,
    1,34A,12,
    17,22,
    55,22,


    So i need to produce a table from the data above that looks like this

    No. Amount
    1-------1
    12-----2
    16-----1
    22-----2
    33-----1
    34A----1
    55------1

    etc.... One thing that i can do, to make things easier, is remove the 34A and just replace it with a number. and i suspect that is probably needed.
    From this information in the 'amount' column, i want to base a pie chart on it.
    The RANGE of the column needs to be selectable elsewhere, ie: i might want to look thru cells A1:A30, or maybe A31:A100 to determine the data i want to analyse. (this is date based, there is another column with the date in , in that row)
    The frequency of numbers, ie: the numbers that we are counting, is variable, but you could say maximum 1 to 100.
    Each row actually looks like this,

    34,12,1,
    16,23,2,45,

    Ie, there is a comma behind each number, including the last number.

    A function found from searching the forum , is this:

    =SUM((LEN(U144:U170)-LEN(SUBSTITUTE(U144:U170,W1,"")))/LEN(W1))

    This works, in an array, with U144:U170 is the area im looking at, and the string im looking for is in cell W1

    however, i further need to amend this to reflect two things....

    1) the range determination needs to come from other cells.
    2) the columns maybe need to be from different sheets, and different column

  2. #2
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting characters (2k)

    SUM((LEN(indirect("&$D$101&":G!$D$102:$D$103)-LEN(SUBSTITUTE(indirect("&$D$101&"'!$D$102:$D$103) ,W1,"")))/LEN(A1))

    not quite working (assuming im on the right track)


    =SUM((LEN(INDIRECT("$D$101!$D$102:$D$103")-LEN(SUBSTITUTE(INDIRECT("$D$101!$D$102:$D$103",A10 0)))/LEN(A100))


    Not working either. think its the back bit.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counting characters (2k)

    Try this. I don't think you can get it from a formula. Select the range and the macro will prompt for where you want the output.

    Steve

    <pre>Option Explicit
    Sub MacroForSteve()
    Dim wksTemp As Worksheet
    Dim rng As Range
    Dim rSource As Range
    Dim rDest As Range
    Dim iCols As Integer
    Dim iCol As Integer
    Dim lLastRow As Long

    Set rSource = Selection.Columns(1)
    Set rDest = Application.InputBox( _
    prompt:="Where do you want the output to start?", _
    Title:="Select a Cell", Type:=8)

    Set wksTemp = Worksheets.Add
    rSource.Copy wksTemp.Range("A1")
    With wksTemp
    lLastRow = .Range("a65536").End(xlUp).Row
    .Range(.Range("a1"), .Cells(lLastRow, 1)).TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, Comma:=True

    iCols = .UsedRange.Columns.Count
    For iCol = 2 To iCols
    .Range(.Cells(1, iCol), .Cells(lLastRow, iCol)).Cut _
    Destination:=.Range("a65536").End(xlUp).Offset(1, 0)
    Next
    .Range("a1").EntireRow.Insert
    .Range("a1") = "a"
    Set rng = .Range(.Range("A1"), .Range("a65536").End(xlUp))

    .PivotTableWizard SourceType:=xlDatabase, _
    SourceData:=rng, _
    TableDestination:=rng.Offset(0, 1)

    With .PivotTables(1)
    .AddFields RowFields:="a"
    .PivotFields("a").Orientation = xlDataField
    .ColumnGrand = False
    .TableRange2.ClearFormats
    .RowRange.Copy rDest.Cells(1).Offset(0, 0)
    .DataBodyRange.Copy rDest.Cells(1).Offset(1, 1)
    End With
    With rDest.Cells(1)
    .Offset(0, 0) = "No."
    .Offset(0, 1) = "Amount"
    End With
    Application.DisplayAlerts = False
    .Delete
    Application.DisplayAlerts = True
    End With
    Set wksTemp = Nothing
    Set rng = Nothing
    Set rSource = Nothing
    Set rDest = Nothing
    End Sub</pre>


    The data will not be sorted as you like, since the numbers will come before the text value(s) [like 34A] but it counts them correctly.

    To sort your list (as written correctly) you can force all of them to be text when you import, by changing the line to:

    <pre> .Range(.Range("a1"), .Cells(lLastRow, 1)).TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, Comma:=True, _
    FieldInfo:=Array(Array(1, 2), Array(2, 2), _
    Array(3, 2), Array(4, 2),Array(5,2))</pre>


    but this will sort numbers "funny" eg 2 will be after the teens, 3 after the twenties.

    There are other manipulations you could do, but you will have to be more specific on if you need text, numbers or both from the output and if you need sorting as if they were all "numbers".

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting characters (2k)

    Thanks Steve, i will give that a bash.

    I knew that fomula was getting a little bit complicated. <img src=/S/beep.gif border=0 alt=beep width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •