1. ## 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. ## 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. ## 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)

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)
.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
.Delete
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. ## 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
•