+ Reply to Thread
Results 1 to 8 of 8

data rearrangement

  1. #1
    Registered User
    Join Date
    01-22-2008
    Location
    qatar
    MS-Off Ver
    office 365
    Posts
    19

    Post data rearrangement

    i have data in columns which need to arranged in a single row. an example file is also attached.
    example


    1 2 3 4 5
    6 7 8 9 10
    11 12 13 14 15
    16 17 18 19 20
    21 22 23 24 25
    26 27 28 29 30
    31 32 33 34 35





    1 2 3 4 5 6 7 8 9 10 ? ? ? ? ? ?



    can anyone help me in generating a formula????
    Thanks
    Attached Files Attached Files
    Last edited by usamampk; 11-15-2020 at 06:25 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,485

    Re: data rearrangement

    Put this in K4:

    =INDEX($D$4:$H$10,INT((COLUMNS($K:K)-1)/5)+1,MOD(COLUMNS($K:K)-1,5)+1)

    then copy it across as required.

    Hope this helps.

    Pete

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    18,980

    Re: data rearrangement

    Since you have not provided what version of Excel you are using, I can only assume it is the latest. Please update your profile to reflect your version. Meanwhile, here is the Power Query solution

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.

  4. #4
    Registered User
    Join Date
    01-22-2008
    Location
    qatar
    MS-Off Ver
    office 365
    Posts
    19

    Re: data rearrangement

    solved,

    thanks for the swift response. much appreciated.
    regards.

  5. #5
    Registered User
    Join Date
    01-22-2008
    Location
    qatar
    MS-Off Ver
    office 365
    Posts
    19

    Re: data rearrangement

    thanks

    works as required.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    18,980

    Re: data rearrangement

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  7. #7
    Registered User
    Join Date
    01-22-2008
    Location
    qatar
    MS-Off Ver
    office 365
    Posts
    19

    Re: data rearrangement

    already done and marked as SOLVED
    regards

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,485

    Re: data rearrangement

    Glad to help - thanks for the rep.

    Pete

+ 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. Data rearrangement
    By akumagaavese in forum Excel General
    Replies: 15
    Last Post: 08-21-2017, 07:06 AM
  2. [SOLVED] Rearrangement of 2 Dimensional Data based on Lookup
    By StratVar in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-17-2017, 03:55 AM
  3. Data rearrangement via VBA
    By Andy308 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-04-2016, 03:57 PM
  4. data rearrangement problem
    By coralnerd in forum Excel General
    Replies: 2
    Last Post: 08-02-2015, 09:46 PM
  5. [SOLVED] Data rearrangement
    By akumagaavese in forum Excel General
    Replies: 3
    Last Post: 08-26-2014, 04:02 PM
  6. Data Rearrangement
    By nasoor123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-19-2010, 03:02 PM
  7. data rearrangement...???
    By nkk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2006, 05:30 PM

Tags for this Thread

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