1 record into multiple record in excel
Please see attached , want to convert 1 line to multiple line with same data , what tool can be used?
Attachment | Size |
---|---|
Test.xlsx | 9.28 KB |
ExcelExperts.comExcel Consultancy, VBA Consultancy, Training and Tips Call:+442081234832 |
|
Excel / VBA ConsultancyFree Training VideosFree SpreadsheetsExcel / VBA JobsNavigationWho's onlineThere are currently 0 users and 298 guests online.
New Excel Experts
Current Excel / VBA Jobs |
1 record into multiple record in excelPlease see attached , want to convert 1 line to multiple line with same data , what tool can be used?
|
Highest Ranked Users
Recent Blogs
ForumsRecent comments
User login |
using a macro in VBA: my
using a macro in VBA:
my solution +>
Option Explicit
Const iRow_Convert_Start As Long = 2
Sub One_To_Multiple()
Dim Extract_of_Col_D_Array() As String
Dim iRow_Convert As Long
Dim iRow_Convert_End As Long
Dim iRow_Into As Long
Dim iRow_Into_Start As Long
Dim iRow_Into_End
Dim j As Long
Dim Ws As Worksheet
Set Ws = ActiveSheet
j = 1
Do While (Ws.Cells(j, 1) <> "")
j = j + 1
Loop
iRow_Convert_End = j - 1
iRow_Into = iRow_Convert_End + 2
Ws.Range("A" & iRow_Into) = "Into"
iRow_Into = iRow_Into + 1
For iRow_Convert = iRow_Convert_Start To iRow_Convert_End
Extract_of_Col_D_Array() = Split(Replace(Ws.Range("D" & iRow_Convert), " ", ""), ",")
For j = LBound(Extract_of_Col_D_Array()) To UBound(Extract_of_Col_D_Array())
Ws.Range("A" & iRow_Into) = Ws.Range("A" & iRow_Convert)
Ws.Range("B" & iRow_Into) = Ws.Range("B" & iRow_Convert)
Ws.Range("C" & iRow_Into) = Ws.Range("C" & iRow_Convert)
Ws.Range("D" & iRow_Into) = Extract_of_Col_D_Array(j)
iRow_Into = iRow_Into + 1
Next
Next
End Sub