+ 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
    2

    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
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    35,393

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

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

    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
    2

    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)

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