Text to Column and Transpose?
 

My Excel skills are fairly minimal and i need help with a very time
consuming project.

One of our program managers has asked me to take the following data:

A1 B1
SOW WBS
3.10.8.0; 3.14.0; 3.12.3.0; 3.12.5.0; 1.1
3.12.7.0; 3.12.8.0; 3.12.9.0

And change the data to:
A B
3.10.8.0 1.1
3.14.0 1.1
3.12.3.0 1.1
3.12.5.0 1.1
3.12.7.0 1.1
3.12.8.0 1.1
3.12.9.0 1.1

I would need to take the SOW reference and have it in its own cell and
have it relate to the WBS from B1 still.
i can do this with text to columns and transpose but i have to do it
on 1000+ cells and it will be very time consuming. If anyone has any
ideas i would be very grateful!
Thanks,
KO

Text to Column and Transpose?
 

You could do this using a macro to text to columnsthen copy/transpose
and then replace blank with 1.1 from the cell containing that value

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"koturtle" wrote in message
...
My Excel skills are fairly minimal and i need help with a very time
consuming project.

One of our program managers has asked me to take the following data:

A1 B1
SOW WBS
3.10.8.0; 3.14.0; 3.12.3.0; 3.12.5.0; 1.1
3.12.7.0; 3.12.8.0; 3.12.9.0

And change the data to:
A B
3.10.8.0 1.1
3.14.0 1.1
3.12.3.0 1.1
3.12.5.0 1.1
3.12.7.0 1.1
3.12.8.0 1.1
3.12.9.0 1.1

I would need to take the SOW reference and have it in its own cell and
have it relate to the WBS from B1 still.
i can do this with text to columns and transpose but i have to do it
on 1000+ cells and it will be very time consuming. If anyone has any
ideas i would be very grateful!
Thanks,
KO

Text to Column and Transpose?
 

On May 10, 8:42*am, "Don Guillett" wrote:
* * * You could do this using a macro to text to columnsthen copy/transpose
and then replace blank with 1.1 from the cell containing that value

* * * If desired, send your file to my address below. I will only look if:
* * * 1. You send a copy of this message on an inserted sheet
* * * 2. You give me the newsgroup and the subject line
* * * 3. You send a clear explanation of what you want
* * * 4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"koturtle" wrote in message

...



My Excel skills are fairly minimal and i need help with a very time
consuming project.

One of our program managers has asked me to take the following data:

A1 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * B1
SOW * * * * * * * * * * * * * * * * * * * * * * * * * * * * WBS
3.10.8.0; 3.14.0; 3.12.3.0; 3.12.5.0; * * * * * * * 1.1
3.12.7.0; 3.12.8.0; 3.12.9.0

And change the data to:
A * * * * * * * * * * *B
3.10.8.0 * * * * *1.1
3.14.0 * * * * * * 1.1
3.12.3.0 * * * * *1.1
3.12.5.0 * * * * *1.1
3.12.7.0 * * * * *1.1
3.12.8.0 * * * * *1.1
3.12.9.0 * * * * *1.1

I would need to take the SOW reference and have it in its own cell and
have it relate to the WBS from B1 still.
i can do this with text to columns and transpose but i have to do it
on 1000+ cells and it will be very time consuming. *If anyone has any
ideas i would be very grateful!
Thanks,
KO- Hide quoted text -

- Show quoted text -

Thank you Don,
Email sent!

Text to Column and Transpose?
 

If I understand your question correctly, this should do what you want...

Sub TransposeSplitDistribute()
Dim X As Long, LastRow As Long, Parts() As String
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = LastRow To 2 Step -1
Parts = Split(.Cells(X, "A"), "; ")
.Rows(X).Offset(1).Resize(UBound(Parts)).Insert
.Cells(X, "A").Resize(UBound(Parts) + 1) = _
WorksheetFunction.Transpose(Parts)
.Cells(X, "B").Offset(1).Resize(UBound( _
Parts)).Value = .Cells(X, "B").Value
Next
End With
End Sub

You might want to try it out on a copy of your worksheet as changes made by
VBA cannot be Undo(ne).

--
Rick (MVP - Excel)



"koturtle" wrote in message
...
My Excel skills are fairly minimal and i need help with a very time
consuming project.

One of our program managers has asked me to take the following data:

A1 B1
SOW WBS
3.10.8.0; 3.14.0; 3.12.3.0; 3.12.5.0; 1.1
3.12.7.0; 3.12.8.0; 3.12.9.0

And change the data to:
A B
3.10.8.0 1.1
3.14.0 1.1
3.12.3.0 1.1
3.12.5.0 1.1
3.12.7.0 1.1
3.12.8.0 1.1
3.12.9.0 1.1

I would need to take the SOW reference and have it in its own cell and
have it relate to the WBS from B1 still.
i can do this with text to columns and transpose but i have to do it
on 1000+ cells and it will be very time consuming. If anyone has any
ideas i would be very grateful!
Thanks,
KO