text to number graph

[sjoerdje92]

hello all,

i cam here with a little problem of mine: i have a series of letter
that are linked to a time but i want to to translate this data into
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 ever
result alone (A,B,C and D) and form a line on which vertically the tim
is shown and horizontally is shown if its an M or an I , so in my lin
of thinking i need to get excel to change every M into exsample a 1 an
every I into a 2 , so i get a line on which i have bigcolums(I) an
small colums(M) and that for every number separate(a,b,c and D) if no
possible they can all mix together...

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

 

[Living the Dream]

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:=xlDatabase,
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").AddDataField
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

 

[Living the Dream]

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.

 

[sjoerdje92]

Apart from needing to proof read things before I flippantly hit the sen

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

 

[Living the Dream]

Anytime

The feedback is appreciated.