+ Reply to Thread
Results 1 to 6 of 6

How to move few columns below another set of columns

  1. #1
    Registered User
    Join Date
    05-04-2020
    Location
    united states
    MS-Off Ver
    office 2007
    Posts
    6

    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.
    Please help.

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

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    05-04-2020
    Location
    united states
    MS-Off Ver
    office 2007
    Posts
    6

    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. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    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.
    Please try the following instead:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  5. #5
    Registered User
    Join Date
    05-04-2020
    Location
    united states
    MS-Off Ver
    office 2007
    Posts
    6

    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. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 10-04-2017, 12:53 PM
  2. Replies: 12
    Last Post: 03-14-2016, 08:42 AM
  3. combine columns with text, move over columns with number
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-30-2015, 02:58 PM
  4. [SOLVED] Macro to move Columns beneath other columns
    By capson in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-05-2014, 02:29 PM
  5. Replies: 1
    Last Post: 12-18-2013, 05:31 PM
  6. [SOLVED] How to automatically sort the columns (move the columns around)
    By OldGrantonian in forum Excel General
    Replies: 4
    Last Post: 08-18-2013, 07:44 AM
  7. Macro required to move Columns and addidng columns with headings
    By sponge_designs in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-03-2009, 08:20 PM

Bookmarks

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