Vba to transpose names and manipulate data

[ScotlandsNeo]

Hi all,

I have a data set which is in a weird format whereby instead of names being in a column at the side of that person's relative data to make a flat database their names are in a row above their data much like a header. each name will have various rows of data below until the next name and so forth).

What I need is to have vba manipulate the data so that it finds their name in the row between characters ":" and "(" , this will capture the name. I then need it to be put in column A beside each of that person's rows of data until the next row comes where it's a new person's name and the process repeats.
The dataset looks like the below:

Owner full name: Scott ( 3 cases)
Case 1- data
Case 2 - data
Case 3 - data
Owner full name: Ryan (1 case)
Case 1- data

I need the names to goto the left of where it says case.

 

[steve the fish]

See if this works. Test on a copy workbook first:

Dim lr As Long, i As Long, stt As Integer, fin As Integer, myVal As String
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lr
    If InStr(LCase(Cells(i, 1)), "name:") > 0 And InStr(LCase(Cells(i, 1)), "(") > 0 Then
        stt = InStr(LCase(Cells(i, 1)), "name:") + 5
        fin = InStr(LCase(Cells(i, 1)), "(")
        myVal = Trim(Mid(Range("A" & i), stt, fin - stt))
    Else
        If Len(myVal) > 0 Then Range("A" & i) = myVal & " " & Range("A" & i)
    End If
Next