Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with Column to Row formatting
I know very little about excel and stumbled across this forum while trying to Google for an answer to my problem. Was hoping you all might be able to help.
I have an excel file like this.... A1---------------B2 Bob Jones-----0;234 Judy Smith----12;14 And I would like to convert that to.... A1--------------B2 Bob Jones----0 Bob Jones----234 Judy Smith---12 Judy Smith---14 Is this possible? The B2 column would have far more numbers and semicolons than the example. I found out ways to separate the semi colons into columns. And the paste special feature with Tranpose. But that puts all the names into Columns, rather than rows. Any help would be much appreciated. Last edited by MadHookUp : June 20th 13 at 04:26 PM Reason: Formatting |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Column to Row formatting
Hi,
Am Thu, 20 Jun 2013 16:25:16 +0100 schrieb MadHookUp: A1---------------B2 Bob Jones-----0;234 Judy Smith----12;14 And I would like to convert that to.... A1--------------B2 Bob Jones----0 Bob Jones----234 Judy Smith---12 Judy Smith---14 do it with a macro: Sub Transpose() Dim LRow As Long Dim i As Long Dim myArr As Variant Dim rngC As Range i = 1 With Sheets("Sheet1") LRow = .Cells(.Rows.Count, 2).End(xlUp).Row For Each rngC In .Range("B1:B" & LRow) myArr = Split(rngC, ";") Sheets("Sheet2").Cells(i, 1).Resize(UBound(myArr) + 1, 1) _ = rngC.Offset(0, -1) Sheets("Sheet2").Cells(i, 2).Resize(UBound(myArr) + 1, 1) _ = WorksheetFunction.Transpose(myArr) i = i + UBound(myArr) + 1 Next End With End Sub Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
|
|||
|
|||
Claus.... you are my hero!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting formula to highlight items on column B inrelation to column A | Excel Discussion (Misc queries) | |||
have B column turn red on conditional formatting of E column: | Excel Worksheet Functions | |||
Please help with Column to row formatting | Excel Worksheet Functions | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
column to column conditional formatting won't work, need formula | Excel Worksheet Functions |