Results 1 to 4 of 4
Thread: Counting characters (2k)

20040708, 11:19 #1
 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
11
122
161
222
331
34A1
551
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

20040708, 12:32 #2
 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.

20040708, 12:39 #3
 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

20040712, 12:32 #4
 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>