Split data

[Deepk]

Hi all,

I want a macro for changing the following data

Input:
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]AB2345
SD7890[/TD]
[TD="class: xl65, width: 64"]AB
XC[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]CB2345
GH6543[/TD]
[TD="class: xl65, width: 64"]SD
FG[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]ER2345
CV3455[/TD]
[TD="class: xl65, width: 64"]GH
MN[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]WX9876
RE3456[/TD]
[TD="class: xl65, width: 64"]HJ
KL[/TD]
[/TR]
</tbody>[/TABLE]

Output:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]AB2345[/TD]
[TD="class: xl65, width: 64"]AB[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]SD7890[/TD]
[TD="class: xl65, width: 64"]XC[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]CB2345[/TD]
[TD="class: xl65, width: 64"]SD[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]GH6543[/TD]
[TD="class: xl65, width: 64"]FG[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]ER2345[/TD]
[TD="class: xl65, width: 64"]GH[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]CV3455[/TD]
[TD="class: xl65, width: 64"]MN[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]WX9876[/TD]
[TD="class: xl65, width: 64"]HJ[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]RE3456[/TD]
[TD="class: xl66, width: 64"]KL[/TD]
[/TR]
</tbody>[/TABLE]

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