+ Reply to Thread
Results 1 to 4 of 4

Problem with data structure

  1. #1
    Registered User
    Join Date
    12-19-2019
    Location
    Jaraguá do Sul, Brazil
    MS-Off Ver
    2016
    Posts
    7

    Question Problem with data structure

    Hello All, first post on the forum!

    I have a spreadsheet thats filled by Production Planning Analysts with the amount of part supplied to the factory each day.

    This way of filling the info is confortable for them, but it makes it impossible for me to make a Pivot Table out of it, so I would like to know:

    1. Is there a way to directly make it into a Pivot table without sorting it?

    2. Is there a way, out of VBA, to sort the data in a way it is possible to make a pivot table? I can do it manually, but thats not what excel is paid for

    I'm working to change the filling method itself, but the amount of backlog data is humongous.

    Thanks!
    Attached Files Attached Files
    Last edited by Saint_jordi; 02-14-2020 at 10:27 AM. Reason: Accordance with post instruction

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Problem with data structure

    Welcome to the forum.

    Instructions telling you how to attach your sample workbook are at the top of the page.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Problem with data structure

    Hello,

    this is what Power Query was made for. You have Excel 2016, so you have Power Query.
    1. turn the input range into an Excel Table with Ctrl+T
    2. Select a cell in the table and on the Data ribbon click "From Table/Range". That will open the data in the Power Query editor.
    3. Select the Supplier column, so it is green
    4. On the Transform ribbon click the "Unpivot Columns" dropdown and select "Unpivot other columns"
    5. rename the column "Attribute" and "Value" to suit your needs.
    6. rename the query in the Properties of the Query Settings panel
    7. On the Home ribbon click "Close and Load" to load the transformed data into the worksheet.

    When new data is added to the original table, all you need to do is refresh the query or click the Data ribbon > Refresh All.

    You may want to change the column headings of the original table to real dates, so Power Query picks up the underlying dates instead of interpreting the data as text. Then you can build better pivot tables that work with the data by dates.

  4. #4
    Registered User
    Join Date
    12-19-2019
    Location
    Jaraguá do Sul, Brazil
    MS-Off Ver
    2016
    Posts
    7

    Re: Problem with data structure

    Hello Teylyn,

    First time I came in contact with Power Query :D

    I still need to fix some tweaks on the spreadsheet that are giving me trouble. But it is solved for the most part.

    Thank you very much!

+ 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. Best way to structure data?
    By nobodyukno in forum Excel General
    Replies: 2
    Last Post: 12-08-2017, 07:43 PM
  2. Creating a Pyramid Hierarchy structure from a flat structure
    By thegamerulez in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-16-2014, 01:28 AM
  3. Structure problem ! Got to read the the problem :)
    By sonu1975 in forum Excel General
    Replies: 2
    Last Post: 04-11-2013, 12:40 AM
  4. "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
  5. Data structure?
    By ahartman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-15-2008, 05:37 PM
  6. [SOLVED] A VB problem with a if structure
    By filo666 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-21-2005, 06:10 PM
  7. data structure and my problem
    By samantha in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-11-2005, 10:06 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