Transpose one column into multiple rows

[abrown1017]

I need to transpose one column of data into multiple rows. The only problem is that there is not a set number of records. It can vary. The only way to identify then end of the records is by the phone number. I have tried multiple macros on my own, and I can't get it to work. Please use SNA records.xlsm That is the most recent.

[patel45]

need a sample file with source data and desired result

[HaHoBe]

Hi, abrown1017,

the code can transpose but it can´t fill in into the adequate columns as the headers/identifiers are missing.

Sub EF944428()
Dim rngStart As Range
Dim rngEnd As Range
Dim wsData As Worksheet
Dim wsNew As Worksheet
Dim lngTarget As Long

Set wsNew = Worksheets.Add
Set wsData = Sheets("Sheet1")

Set rngStart = wsData.Range("A1")
Do While rngStart.Row < wsData.Range("A" & Rows.Count).End(xlUp).Row
  Set rngEnd = wsData.Columns(1).Find(what:="(", after:=rngStart, lookat:=xlPart)
  If Not rngEnd Is Nothing Then
    wsData.Range(rngStart, rngEnd).Copy
    lngTarget = lngTarget + 1
    wsNew.Range("A" & lngTarget).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Set rngStart = rngEnd.Offset(1, 0)
  Else
    MsgBox "Can´t find the telephone for " & rngStart.Value, vbInformation, "Exit Sub"
    Exit Sub
  End If
Loop
End Sub

@patel45

need a sample file with source data and desired result

Opening post hasn´t been edited so the file was there with the data and the result before your helpfull post - what else do you need?

Ciao,
Holger

[abrown1017]

The code you sent me worked great. That is all I needed! Thank you so much