Macro to delimit and create column headers

Re: Macro to delimit and create column headers

Will each string have the same format - in your example they all seem to CPU Sync Wait Suspension ? So in essence the first string will determine the columns then the remainder of the strings you only need to get the data.

Re: Macro to delimit and create column headers

The easiest way to do this would be with regular expressions. Will there only just be the 4 headers? You just want the data extracted? On my way home. Will post solution when I get home if someone else not picked thread up.

Re: Macro to delimit and create column headers

Hi..

Assuming you have already taken care of those commas (at start and end of string).. this should do it..

Check the attachment to see it working..

Private Sub CommandButton1_Click()
    Dim add As String, it, j As Long, rngData As Range, vArr
    With CreateObject("scripting.dictionary")
        With Sheets("Input").Range("A1").CurrentRegion
            Set rngData = .Offset(1).Resize(.Rows.Count - 1)
        End With
        For Each it In rngData
            add = it.Address
            it = Split(it, ",")
            For j = LBound(it) To UBound(it)
                Z = Split(it(j), ":")(1)
                If .Count = 0 Then
                    .Item(y) = Z
                Else
                    .Item(y) = .Item(y) & "," & Z
                End If
                x0 = .Item(it(j))
            Next j
            ZZ = Join(.Items, " ")
            Sheets("Output2").Range(add).Resize(, .Count - 1) = Split(ZZ, ",")
            .RemoveAll
        Next it
    End With
    Sheets("Output2").Range("A1").Resize(1, 4).Value = Array("CPU", "Sync", "Wait", "Suspension")
End Sub

Re: Macro to delimit and create column headers

Hi..

You could just do a find and replace on your raw data to remove the "ms"..

Just below this line:

With Sheets("Input").Range("A1").CurrentRegion

add this...

.Replace What:="ms", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False

As for dividing by 1000.. if no one chimes in.. I will take a look at that later or tomorrow.. (missus wants to watch a movie right now)...

Cheers

Re: Macro to delimit and create column headers

or

Sub M_snb()
    With Sheets("input").Columns(1)
        .Replace "CPU: ", ""
        .Replace "Suspension: ", ""
        .Replace "Wait: ", ""
        .Replace "Sync: ", ""
        .Replace " ms", ""
        .Replace "-", "0"
        .TextToColumns , , , , False, False, True, False, False
    End With
End Sub

Re: Macro to delimit and create column headers

Hey snb..

Surely my 23 lines of code with a couple of loops is more efficient than your 9 lines of code using native excel functions.. :tongue:

There is so much knowledge here at Ozgrid amongst the MVPS/Dev Members and normal members alike..

Gotta love it..

Re: Macro to delimit and create column headers

Did you use application.screenupdating=false ?

Re: Macro to delimit and create column headers

ahh.. that would have made it 25 lines.. :crying: