Split data

[Deepk]

Hi all,

I want a macro for changing the following data

Input:

AB2345 SD7890	AB XC
CB2345 GH6543	SD FG
ER2345 CV3455	GH MN
WX9876 RE3456	HJ KL

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Output:

AB2345	AB
SD7890	XC
CB2345	SD
GH6543	FG
ER2345	GH
CV3455	MN
WX9876	HJ
RE3456	KL

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Thanks in advance.

 

[mohadin]

Hi
sorry deleted for miss understanding

 

[mohadin]

Is this what you want?

Your ata in a1:b4

Sub test()
    For i = 1 To 4
        For j = 1 To 2
            a = Split(Cells(i, j), Chr(10))
            Cells(i, j).Offset(i - 1, 3) = a(0)
            Cells(i, j).Offset(i, 3) = a(1)
        Next
    Next
End Sub

 

[Deepk]

Is this what you want?

Your ata in a1:b4

Sub test()
    For i = 1 To 4
        For j = 1 To 2
            a = Split(Cells(i, j), Chr(10))
            Cells(i, j).Offset(i - 1, 3) = a(0)
            Cells(i, j).Offset(i, 3) = a(1)
        Next
    Next
End Sub

Excellento..

Thank you very much.

 

[Peter_SSs]

How big (number of rows) is your actual data?

 

[Deepk]

How big (number of rows) is your actual data?

hi Peter,

Three to four thousand rows.

Regards

 

[Peter_SSs]

hi Peter,

Three to four thousand rows.

Regards

OK thanks. That is actually pretty small for vba so the fact that this is roughly 30 times faster probably wouldn't be noticeable.
If there was a lot more data this would begin to show up as noticeably faster.

Sub Split_Data()
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long
  
  a = Range("A1", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a) * 2, 1 To 2)
  For i = 1 To UBound(a)
    c = Split(a(i, 1), Chr(10))
    b(i * 2 - 1, 1) = c(0)
    b(i * 2, 1) = c(1)
    c = Split(a(i, 2), Chr(10))
    b(i * 2 - 1, 2) = c(0)
    b(i * 2, 2) = c(1)
  Next
  Range("D1:E1").Resize(UBound(b)).Value = b
End Sub

 

[Deepk]

OK thanks. That is actually pretty small for vba so the fact that this is roughly 30 times faster probably wouldn't be noticeable.
If there was a lot more data this would begin to show up as noticeably faster.

Thank you Peter. Its working.

 

[Peter_SSs]

Thank you Peter. Its working.

You're welcome.
Just repeating that for 3 or 4 thousand rows it wouldn't matter which of the suggested codes you use - whatever you feel most comfortable with.
If you had 200,000 rows then I would definitely use the code I suggested.

 

[Rick Rothstein]

Here is one more macro to consider (what is interesting about this one is it does not use a loop)...

Sub Split_Data()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C1").Resize(2 * LastRow) = Application.Transpose(Split(Join(Application.Transpose(Range("A1:A" & LastRow)), vbLf), vbLf))
  Range("D1").Resize(2 * LastRow) = Application.Transpose(Split(Join(Application.Transpose(Range("B1:B" & LastRow)), vbLf), vbLf))
End Sub