How to create marcro to parse and tranpose excel data

[boyitlog]

I would like to create an excel macro to parse data from this:

name NN
site SA
cell CA
dataA DTACA
dataB DTBCA
dataC DTCCA
dataE DTECA
dataF DTFCA
end #
name NN
site SA
cell CB
dataA DTACB
dataB DTBCB
dataC DTCCB
dataE DTECB
dataF DTFCB
end #
name NN
site SA
cell CC
dataA DTACC
dataB DTBCC
dataF DTFCC
end #

to this:

name site cell dataA dataB dataC dataE dataF
NN SA CA DTACA DTBCA DTCCA DTECA DTFCA
NN SA CB DTACB DTBCB DTCCB DTECB DTFCB
NN SA CC DTACB DTBCB DTFCC

Any help will be much appreciated.

 

[doofusboy]

Something like this should work for you:

Dim lngLastRow As Long
Dim lngCTRx As Long
Dim lngCTRy As Long
Dim strTransposedValues As String
    lngCTRy = 1
    lngLastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    For lngCTRx = 1 To lngLastRow
        If UCase(Split(Range("A" & lngCTRx).Value, " ")(0)) = "NAME" Then
           strTransposedValues = Split(Range("A" & lngCTRx).Value, " ")(1) & " "
        ElseIf UCase(Split(Range("A" & lngCTRx).Value, " ")(0)) = "END" Then
           Range("D" & lngCTRy).Value = Left(strTransposedValues, Len(strTransposedValues) - 1)
           lngCTRy = lngCTRy + 1
        Else
            strTransposedValues = strTransposedValues & Split(Range("A" & lngCTRx).Value, " ")(1) & " "
        End If
    Next lngCTRx