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.

 

[Rick Rothstein]

You are probably going to use Peter's new code in Message #20 , but, for the record, here is my code modified to handle your new data structure. Note that I have assumed the gaps you show between the groups of values is to delineate one cell's values from another and does not represent a blank cell on the worksheet between them.

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

 

[Deepk]

You are probably going to use Peter's new code in Message #20 , but, for the record, here is my code modified to handle your new data structure. Note that I have assumed the gaps you show between the groups of values is to delineate one cell's values from another and does not represent a blank cell on the worksheet between them.

Thank you Rick and Peter. you people are are awesome.

 

[Peter_SSs]

Cheers. Good luck with the project!