# How to move few columns below another set of columns

1. ## How to move few columns below another set of columns

I have data/table with multiple rows with 6 columns.

I have such tables/data (hundreds of them) in one excel sheets separated by two empty rows.. each data/table must be considered as one set.

All I want is to copy the last three columns (all the rows) below the first three 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.

I have hundreds of sets in one excel sheet. So, I need a formula or program for this purpose.

2. ## Re: How to move few columns below another set of columns

This proposal employs two helper columns (G:H) which may be moved and/or hidden for aesthetic purposes.
Column G is populated using: =IF(A2="","",IF(ISTEXT(A2),12*COUNTIFS(A\$2:A2,"?*")-10,SUM(G1,1)))
Column H is populated using: =IF(G2="","",SUM(G2,5))
The final output (blue highlighted range) is populated using: =IFNA(IFERROR(INDEX(A\$2:A2,MATCH(ROW(),\$G\$2:\$G2,0)),INDEX(D\$2:D2,MATCH(ROW(),\$H\$2:\$H2,0))),"")
Note that the original data is shifted down one row.
Let us know if you have any questions.

3. ## Re: How to move few columns below another set of columns

Thanks a ton for your help and sorry for the delay in the reply.
I could generate column G and H with the formula you have provided.
But I cannot generate the final highlighted output. I'm getting "invalid name error" inside the cell where I entered the formula it shows "#NAME?"

4. ## Re: How to move few columns below another set of columns

Sorry, I did not remember that IFNA is not supported by the 2007 version.
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

5. ## Re: How to move few columns below another set of columns

Thank you. It works!

But, as I mentioned in my question, I have hundreds of such sets in one sheet.
When I generated H, I edited the formula according to the number of rows per set (which is 8).
I could generate G and H, but they are not continuous numbering for the next set (see attached image).
There are two rows between each set. so, what am I missing?Attachment 677561

6. ## Re: How to move few columns below another set of columns

Selecting Attachment 677561 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator"
Please utilize the instructions in the banner at the top of the page to upload an .xlsx file as you did in post #1.
Let us know if you have any questions.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1