How to move data in columns below another set of columns

I have data in multiple rows below 9 columns, something like this:
ABCDEFGHI
111222333
111222333

I want the data below the columns DEF and GHI below ABC, so that they look like this:
ABC
111
111
222
222
333

I tried to copy and paste the data below DEF and GHI below ABC, but I get this message "You can't paste this here because the copy area and paste area aren't the same size".

Basically, I have to select the 'paste area' before pasting, so that the copied cells fit into it exactly.

Since the data is enormous, it is tedious to do it manually.

I just wish to ask the experienced people over here if there is a simpler way of getting this done?

Thank you!

Please Login or Register  to view this content.

You cannot copy the entire column and then paste it to a smaller number of rows. Only copy or cut the rows with data.

Thank you JLGWhiz and Glenn Kennedy for your time and valuable suggestions!

Both of your solutions work perfectly!

For the table given below:
Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 Col 7 Col 8 Col 9
1 2 3 4 5 6 7 8 9
11 12 13 14 15 16 17 18 19
21 22 23 24 25 26 27 28 29

Result with JLGWhiz's code:
Col 1 Col 2 Col 3
1 2 3
11 12 13
21 22 23
Col 4 Col 5 Col 6
4 5 6
14 15 16
24 25 26
Col 7 Col 8 Col 9
7 8 9
17 18 19
27 28 29

Result with Glenn Kennedy's formula:
Col 1 Col 2 Col 3
1 2 3
4 5 6
7 8 9
11 12 13
14 15 16
17 18 19
21 22 23
24 25 26
27 28 29

However, since I am not well acquainted with excel functions or VBA, I am not able to modify them to exactly suit my actual needs. I had in fact given a simple example to quickly explain my situation.

I had tried tweaking the provided solutions, and also tried searching the internet, but was not completely successful.

I would be much thankful if you could help me out with the following:

1) What if the number of columns needed were more than 3? (e.g. ABCD instead of ABC).
I was able to successfully tweak JLGWhiz's code to suit my needs, as I had to just change the range of cells which needs to be cut and pasted.
However, to me the formula by Glenn Kennedy was more difficult to crack.

2) What if the first column is specific to each row and has to be retained along with the contents of the row?

Example:
NAME Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 Col 7 Col 8 Col 9
AAA 1 2 3 4 5 6 7 8 9
BBB 11 12 13 14 15 16 17 18 19
CCC 21 22 23 24 25 26 27 28 29

Needed result:
NAME Col 1 Col 2 Col 3
AAA 1 2 3
AAA 4 5 6
AAA 7 8 9
BBB 11 12 13
BBB 14 15 16
BBB 17 18 19
CCC 21 22 23
CCC 24 25 26
CCC 27 28 29

For this second issue, I was able to modify neither of the provided solutions.

Once again I thank you both for your generous help!

That was perfect!

Glenn Kennedy you are awesome!

Thanks a ton!

Hello... I have a similar problem.. In my case, I have set of numbers (hundreds of them) separated by two empty rows..
I want to merge them based on each set.

for instance, for the below table,
col1 col2 col3 col4 col5 col6
1 2 3 4 5 6
11 12 13 14 15 16
21 22 23 24 25 26
31 32 33 34 35 36


col7 col8 col9 col10 colx coly
7 8 9 10 x1 y1
17 18 19 20 x2 y2
27 28 29 30 x3 y3
37 38 39 40 x4 y4


result must be

col1 col2 col3
1 2 3
11 12 13
21 22 23
31 32 33
col4 col5 col6
4 5 6
14 15 16
24 25 26
34 35 36


col7 col8 col9
7 8 9
17 18 19
27 28 29
37 38 39
col10 colx coly
10 x1 y1
20 x2 y2
30 x3 y3
40 x4 y4

Hello... I have a similar problem.. In my case, I have set of numbers (hundreds of them) separated by two empty rows.. each must be considered as a set.

I want to merge the columns as rows based on each set.

for instance, for the below table,
col1 col2 col3 col4 col5 col6
1 2 3 4 5 6
11 12 13 14 15 16
21 22 23 24 25 26
31 32 33 34 35 36


col7 col8 col9 col10 colx coly
7 8 9 10 x1 y1
17 18 19 20 x2 y2
27 28 29 30 x3 y3
37 38 39 40 x4 y4


result must be

col1 col2 col3
1 2 3
11 12 13
21 22 23
31 32 33
col4 col5 col6
4 5 6
14 15 16
24 25 26
34 35 36


col7 col8 col9
7 8 9
17 18 19
27 28 29
37 38 39
col10 colx coly
10 x1 y1
20 x2 y2
30 x3 y3
40 x4 y4

If this is not clear, I have attached a table with two example sets and expected results.
Please help.

Hello Ford, Thanks for the clarification.

I have submitted my query as a new thread.