Forum Discussion
Data organized in Columns instead of Rows - Excel
Hi, In excel, I have a long list of data that I want to organized in 4 columns (First Name, Last Name, Phone and Email), the thing is that the data I have is in one column, so like in the image, ...
Sub transform()
Dim i As Long, j As Long, k As Long
Dim MyArray() As String
Dim MyString As String
Range("D:G").Clear
i = Range("A" & Rows.Count).End(xlUp).Row
k = 1
For j = 1 To i
Select Case j Mod 3
Case Is = 1
MyString = Cells(j, 1).Value
MyArray = Split(MyString)
Cells(k, 4).Value = MyArray(0)
Cells(k, 5).Value = MyArray(1)
Case Is = 2
Cells(k, 6).Value = Cells(j, 1).Value
Case Is = 0
Cells(k, 7).Value = Cells(j, 1).Value
k = k + 1
End Select
Next j
End Sub
This code returns the intended result in my sheet.