+ Reply to Thread
Results 1 to 2 of 2

Data Transpose Needed!

  1. #1
    Registered User
    Join Date
    07-25-2013
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    Excel 2007
    Posts
    9

    Data Transpose Needed!

    Hi there:

    I need to change the format/structure of a raw data file to be able to use in some statistics software for my research work. It seems that the raw data need to be transposed for each firm. But it would take a long while to do it if I want to do it manually over and over again for hundreds of different firms. I was wondering if there is any mechanisms in excel to make it faster or automate the process to some extent.

    I have shown here a simplified image of my intended format/structure of the data file. I hope you understand that the real raw data file has a lot of firms, a lot more items (14), and years(16). I have also attached a sample of the raw data file for you to have a look and understand the nature of the data file.

    Excel Help.png

    I would highly appreciate if somebody can help me out.

    Thanks.

    Sincerely,
    Imran
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,395

    Re: Data Transpose Needed!

    Hi Imran,

    You need to UnPivot your data to make a row for each stock and year. Then pivot it again using the Item as a column in the Pivot Table.

    I'm using Excel 2016 and can do this problem in about 10 mouse clicks using the newer tool called Power Query and its UnPivot tool. Sadly you have 2007 Excel that doesn't have the ability to do Power Query. Also the result of my 10 mouse clicks gives a 3 meg file which is too large to load up the this forum.

    PQ UnPivot then Pivot Answer Only.xlsx

    See the above attached where I needed to delete all the extra sheets and give back the answer only.
    Last edited by MarvinP; 06-13-2017 at 09:56 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

+ 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. Correction needed for transpose macro
    By genetist in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2014, 04:03 AM
  2. Replies: 1
    Last Post: 06-30-2014, 07:23 PM
  3. Help needed for a MACRO to transpose data from multiple variable columns into rows.
    By excel_starter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2014, 02:30 PM
  4. [SOLVED] VBA / Macro / Formula help needed for extracting data and Transpose into another sheet
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 02-05-2014, 12:19 PM
  5. [SOLVED] Need to Transpose and Flip Table of Data - Macro Probably Needed
    By BobTomlinson in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-08-2013, 09:25 AM
  6. [SOLVED] VB code Macro needed to transpose data from columns to rows
    By raw_geek in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-20-2012, 08:54 AM
  7. [SOLVED] Help needed in excel transpose
    By aswin_aditi@hotmail.com in forum Excel General
    Replies: 0
    Last Post: 05-02-2006, 02:30 AM

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