Excel Macro to Move Data in Columns below other columns

Question

I have data in 9 columns from A to I. What I need to do is move D,E,F and G,H,I below A,B,C so it's 3 columns. The number of rows I have is variable.

So My data looks like this right now

A B C D E F G H I   
1 2 3 4 5 6 7 8 9

I need it to look like this:

A B C
1 2 3 
4 5 6 
7 8 9 

Answer 1

Looks like a job for the FREE FREELANCER!

In today's episode...

Little Boy: Can I get an answer for the question, please?

Free Freelancer: But of course, Little Boy! I daresay, this is a job for our wonderful friend, Arrays!

LB: What are arrays, Mr. FFL?

FFL: You can Google that and it'll give you upwards of 11 million results. Don't ask me!

LB: But how will that help us here?

FFL: Easily enough, it allows us to imagine and create matrices! Since OP wants a matrix-like arrangement of a one-dimensional (1D) set of data, we can assign it to a matrix-like array that we can then transfer back to Excel!

LB: I... don't... understand. Please help me! Just give me an answer, please?

FFL: But I'm not done yet! Have some backbone, little boy, to understand this stuff! First off, let's do it the long way! Let's create an array of 3 rows and 3 columns!

Dim Arr(1 to 3, 1 to 3) As Variant

FFL: ... There, we have an array! It's a simple x-y plot that we can populate with our data. Since he only has 9 data points he wants to arrange in a 3x3 matrix, we know the size we need to set the array to.

LB: Hey, that looks easy enough, FFL!

FFL: IT IS! Now, see the following. For each "coordinate" in our plot, let's assign a value.

Arr(1, 1) = Cells(2, 1).Value
Arr(1, 2) = Cells(2, 2).Value
Arr(1, 3) = Cells(2, 3).Value

Arr(2, 1) = Cells(2, 4).Value
Arr(2, 2) = Cells(2, 5).Value
Arr(2, 3) = Cells(2, 6).Value

Arr(3, 1) = Cells(2, 7).Value
Arr(3, 2) = Cells(2, 8).Value
Arr(3, 3) = Cells(2, 9).Value

FFL: Easily enough, we've been able to plot the values now to our array. Now I know that the third row and second column of the array will have the value of the cell in the second row and eighth column in the worksheet!

LB: Wow, that makes it look easy-peasy! Thanks Mr. FFL!

FFL: You're welcome, little boy! But not so fast, I see something in the 9 lines above... I see a... PATTERN! There is a pattern, little boy. Be so kind to tell me!

LB: Pattern? But all I see are consecutive numbers and stuff!

FFL: But of course! Consecutive numbers are good for us because we can use... FOR LOOPS! Now let's simplify the above... I know I want 3 rows and 3 columns. And I know that my values are in 9 different cells. What I'll do is iterate over each row then by each column of my array and assign values to them. Since the column of my desired value changes, I'll increment it inside my innermost loop, so my "pointer" moves!

LB: Mr. FFL, don't scare me with all that stuff!

FFL: Ah, balls do make the man, so let's move on to code!

ColIndex = 1
For Iter1 = 1 To 3
    For Iter2 = 1 To 3
        Arr(Iter1, Iter2) = Cells(2, ColIndex).Value
        ColIndex = ColIndex + 1
    Next
Next

FFL: There, I've reduced it to a fairly simple and intuitive loop! The advantage to this is that if OP wants to create larger plots based on longer ranges of cells, he'll only use these few lines to create them!

LB: WOW! That looks awesome, mister! But how do I put it back, how do I?

FFL: Let's do another loop then! What a dummy you are, little boy!

For i = 1 To 3
    For j = 1 To 3
        Cells(i + 1, j).Value = Arr(i, j)
    Next
Next

FFL: Mwahahaha! Now we've filled the cells we want with all the stuff from that long line of values! I will leave with you, little boy, the way to delete unwanted columns and stuff.

LB: But... But... Mr. FFL, why didn't you just equate the value of the new cells to the value of the old cells in your loop? Why use arrays?

FFL: ... Err... Hnggrrr... Why do you care? IT'S FREE!!!

Until next time on THE FREE FREELANCER!

EPISODE RECAP (Entire code):

Sub TransformToTable()

    Dim Arr(1 To 3, 1 To 3) As Variant

    ColIndex = 1
    For Iter1 = 1 To 3
        For Iter2 = 1 To 3
            Arr(Iter1, Iter2) = Cells(2, ColIndex).Value
            ColIndex = ColIndex + 1
        Next
    Next

    For i = 1 To 3
        For j = 1 To 3
            Cells(i + 1, j).Value = Arr(i, j)
        Next
    Next

End Sub

Answer 2

What @user1759942 mentioned is generally true, however the code has already been written, albeit in Word. Copy your data and Paste Special... as Unformatted Text into Word. Select All, Insert > Tables - Table and Convert Text to Table... with Number of columns: 3. Copy back in to Excel and delete second and third rows.