Do I need a Macro to do Transpose in this case?

[Ting]

I have worksheets with column A showing account number, with multiple phone
numbers associated to this account in a comment bubble, coulumn B showing the
carrier name.
What I am trying to do is to get these data into 3 columns, one row for each
phone number depending on how many records in each comment, also with
repeating account and carrier description for example:

Account number phone number carrier
12345 123-123-1234 AT&T
12345 123-234-2345 AT&T
12345 234-334-3345 AT&T

I had tried copy phone nubmers from comment to column C, then do Text to
Column, and TRANSPOSE but this will take a year for me to finishe all the
worksheets. I am hoping there are some easier way to do this...

Help !
And thanks in advance!!

 

[Bernie Deitrick]

Ting,

Yes, you need a macro.

Select the cells in column A, and run the macro below. Try it on a copy of your data first, to make
sure it works....

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myC As Range
Dim myCom As String
Dim myComs As Variant
Dim i As Integer
Dim myR As Long
Dim myS As Range

Set myS = Selection
On Error GoTo noComment
myS.Offset(0, 1).EntireColumn.Insert
For myR = myS.Cells.Count To 1 Step -1
With myS.Cells(myR)
myCom = .Comment.Text
.Comment.Delete
myComs = Split(myCom, Chr(10))
.Offset(1).Resize(UBound(myComs), 3).Insert
.Resize(1, 3).Copy .Offset(1).Resize(UBound(myComs) - LBound(myComs), 3)
For i = 0 To UBound(myComs)
.Offset(i, 1).Value = myComs(i)
Next i
End With
GoTo skip
noComment:
Resume TryAgain
TryAgain:
On Error GoTo noComment
skip:
Next myR
End Sub

 

[Ting]

OH MY GOD !

Is this a magic or what?!!
You save my day(s)!

Thanks you very so much!!! Bernie

 

[Bernie Deitrick]

Yes, it is magic... and a lucky guess that you were using a return between numbers... ;-)

Thanks for letting me know that it worked for you - always nice to get positive feedback.

Bernie
MS Excel MVP