Hi All,
I am new to vba macros. Can anybody help me with the simple vba code to perform following operation.
I have data in two columns with multiple rows as follows:
Column A Column B
A A1 | A2 | A3
B B1 | B2
C C1 | C2 | C3 | C4
D D1 | D2 | D3 | D4 | D5
...
......
........
I need the final output in the following format:
Column C Column D
A A1
A A2
A A3
B B1
B B2
C C1
C C2
C C3
C C4
D D1
D D2
D D3.......................
...
......
........
Any help would be appreciated
'ROWS TO COLUMNS
Here's a macro for expanding rows of data to multiple rows parsing out the delimited values in one column while duplicating all the others.Just edit the Col = 3 code to Col = 2 and run it on a copy of your data, should do what you want. There's a sample workbook too you could drop your data into and test it out.
Last edited by JBeaucaire; 12-26-2019 at 03:48 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thank you for prompt response. The macro worked smoothly. However, I feel only problem with macro is that, original data gets replaced by new dataset.
Last edited by JBeaucaire; 12-26-2019 at 03:48 PM.
Hi ! As a starter try this demonstration !
PHP Code:Sub Demo()
L& = 1
For R& = 1 To Cells(Rows.Count, 2).End(xlUp).Row
SP = Split(Cells(R, 2).Value, " | ")
N& = UBound(SP) + 1
With Cells(L, 3).Resize(N, 2).Columns
Cells(R, 1).Copy .Item(1)
.Item(2).Value = Application.Transpose(SP)
L = L + N
End With
Next
End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks