+ Reply to Thread
Results 1 to 4 of 4

How to change data structure (un-pivot essentially)

  1. #1
    Registered User
    Join Date
    04-24-2008
    Posts
    85

    Question How to change data structure (un-pivot essentially)

    Hey, is there a systematic way of doing what I want - and I don't know the proper name for this, but essentially - I want to get the data in a proper format for database use, and I currently have it structured in a matrix form. (I view it as essentially un-pivoting this data)

    For example, this CSV file: http://financials.morningstar.com/aj...2CSV.html?t=FB

    Shows up in pivot format:
    - Row Group: Ratios,
    - Column Group: Time,
    - Values = Results

    Is there a way of changing this structure so that it looks like this instead:
    - Column A: Ratio
    - Column B: Time
    - Column C: Result

    And then, if I was to pivot this, I can produce the matrix view that the CSV originally had.
    Anyone have a next step or two I can investigate?
    Thanks,

    Adam

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: How to change data structure (un-pivot essentially)

    So there is a way but that is quite a specific task since your data layout is fairly unique.
    Are you familiar with VBA at all?

    You COULD use PowerQuery after a little bit of massaging if you are not as comfortable with VBA.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    04-24-2008
    Posts
    85

    Re: How to change data structure (un-pivot essentially)

    In VBA, I'd say I have beginner skills - I've edited several different scripts and made a few of my own - usually through a combination of recording and acquiring bits of code from across the web. To approach this problem, I'll likely do the same. Here's what I'm thinking:
    1. For each row containing data, create as many rows as there is data values
    2. Then create two new columns, one for time period and one for the result
    3. Copy and paste (with transpose) into the newly created rows
    4. Continue for each row, then delete the rest of the columns

    I think this will work. Somehow I assumed there would've been an "unpivot" type of code already out there which I could take advantage of. Anyway, thanks for the help and if you have any suggestions - I'm interested. I'll also check out PowerQuery - have not heard of this before, so will investigate. Thanks,

    Adam

  4. #4
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: How to change data structure (un-pivot essentially)

    There is plenty of code by googling UNPIVOT data, so you can start there.

+ 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. Need to create a table that essentially filters based upon one criteria
    By GreenSmoak in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 03-11-2016, 04:08 PM
  2. [SOLVED] How to change data structure by month #'s
    By stephme55 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-30-2015, 06:21 PM
  3. Organizational Structure with Pivot
    By fritz_os in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-22-2015, 02:14 AM
  4. Replies: 1
    Last Post: 07-30-2012, 11:27 AM
  5. VBA to change structure of excel file
    By Jedgroev in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-21-2012, 09:13 AM
  6. "convert" data with logfile structure to spreadsheet structure
    By boarders paradise in forum Excel General
    Replies: 7
    Last Post: 01-10-2011, 02:06 AM
  7. Replies: 1
    Last Post: 09-07-2010, 01:15 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