text to number graph
 

hello all,

i cam here with a little problem of mine: i have a series of letters that are linked to a time but i want to to translate this data into a column graph.
my data looks like this:
7.10 BM
7.72 DM
10.61 AM
11.01 CM
17.33 AM
18.44 CM
22.72 CI
27.35 AM
32.22 CM
34.39 CM
35.02 AI
41.56 AM
47.01 CM
48.62 CI
64.04 CM
66.35 BI
71.16 CM
76.43 CM
80.54 DI
84.55 BM
92.20 AI

and this gos on for a while but my idea was to make a macro for every result alone (A,B,C and D) and form a line on which vertically the time is shown and horizontally is shown if its an M or an I , so in my line of thinking i need to get excel to change every M into exsample a 1 and every I into a 2 , so i get a line on which i have bigcolums(I) and small colums(M) and that for every number separate(a,b,c and D) if not possible they can all mix together...

so thats my idea and i've been trying everything i know i can do with excel but i cant figure it out can someone help me?

text to number graph
 

Hi

This will change your M's into 1's, and your I's into 2's.

Sub ChangeI()
Dim RowNum As Integer
RowNum = 2
Range("B2").Select
Do
Replacement = ActiveCell.Value
Range("B" & RowNum).Select
Selection.Replace What:="I", Replacement:=2, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
RowNum = RowNum + 1
Range("B" & RowNum).Select
Loop Until ActiveCell.Value = ""
End Sub

Sub ChangeM()
Dim RowNum As Integer
RowNum = 2
Range("B2").Select
Do
Replacement = ActiveCell.Value
Range("B" & RowNum).Select
Selection.Replace What:="M", Replacement:=1, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
RowNum = RowNum + 1
Range("B" & RowNum).Select
Loop Until ActiveCell.Value = ""
End Sub

.................................................. ...............


As for your Graph, I wasn't sure as to what style of vraoh your were
wanting, so I played around with a Pivot table that does the following:

Now, I have used 00.00 to represent blank cells as it give a better
alignment below.

A1 00.00 00.00 10.61 00.00 17.33 00.00 00.00 27.35
A2 00.00 00.00 00.00 00.00 00.00 00.00 00.00 00.00
B1 07.10 00.00 00.00 00.00 00.00 00.00 00.00 00.00
B2 00.00 00.00 00.00 00.00 00.00 00.00 00.00 00.00
C1 00.00 00.00 00.00 11.01 00.00 18.44 00.00 00.00
C2 00.00 00.00 00.00 00.00 00.00 00.00 22.72 00.00
D1 00.00 07.72 00.00 00.00 00.00 00.00 00.00 00.00
D2 00.00 00.00 00.00 00.00 00.00 00.00 00.00 00.00

If this is kin of what you are looking for, I have a Recorded Macro
below, which does the job and creates the above on a new sheet. One of
the other aptly qualified contributors may have a neater option for you.

HTH
Mick.

Sub PivotMyData()
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase,
SourceData:= _
"Sheet1!R1C1:R22C2",
Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet6!R3C1", TableName:="PivotTable3",
DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet6").Select
Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("YourCode")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("YourVal"), "Sum of YourVal", xlSum
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("YourVal")
.Orientation = xlColumnField
.Position = 1
End With
Range("A1").Select
End Sub

text to number graph
 

Apart from needing to proof read things before I flippantly hit the send
button, I also neglected to mention that you will have to alter the
ranges to suit yours in order for it to run properly.

Cheers
Mick.

Well this is pretty Munch what i was looking for thanks!

text to number graph
 

Anytime

The feedback is appreciated.