Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose Column to Row | Excel Worksheet Functions | |||
column 2 to transpose using column 1 as reference | Excel Discussion (Misc queries) | |||
Transpose a text string while copying adjacent column data to new | Excel Worksheet Functions | |||
transpose column to a row? | Excel Discussion (Misc queries) | |||
macro to transpose cells in Column B based on unique values in Column A | Excel Programming |