+ Reply to Thread
Results 1 to 8 of 8

Data consolidation into Data Model

  1. #1
    Registered User
    Join Date
    03-25-2015
    Location
    Boston
    MS-Off Ver
    2016
    Posts
    6

    Data consolidation into Data Model

    Hi All!

    I have two worksheets linked to a SharePoint database with several columns of dates corresponding to different IDs. I want to pool all of those dates together so that I can see what IDs (with accompanying descriptions) are coming up this upcoming week, month, etc.

    date table tests.JPG

    Right now, I run a macro to do this manually. However, every time I refresh my linked data, I have delete my old table to run the macro again.

    Is there an easier way consolidate all this data together through a single pivot table, and have each column header and table called out? It would look similar to my manual macro generated table, but would be able to update dynamically. I have Excel 2016.

    Thanks!
    Attached Files Attached Files
    Last edited by moneguia; 07-27-2017 at 11:27 PM. Reason: Fixed title of post for clarity

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Column headers from table into pivot table rows

    Add your tables to Data Model then create relationship(s) between them and next create Pivot Tables as you wish
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Registered User
    Join Date
    03-25-2015
    Location
    Boston
    MS-Off Ver
    2016
    Posts
    6

    Re: Column headers from table into pivot table rows

    I have a few columns between the ID column and the date columns. How do I exclude those columns from the Data Model?

    ie:
    ID Column1 Initial Date Baseline Date Final Date
    1107 descrip 1 10/4/2017 10/29/2017 11/15/2017
    1111 descrip 2 1/29/2017 3/6/2017 4/14/2017
    1136 descrip 3 2/9/2017 2/26/2017 3/16/2017

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Column headers from table into pivot table rows

    Why?
    If you don't want to see "unneccessary" columns in the PivotTable fields you can right click on selected column(s) in the DataModel and then select Hide

  5. #5
    Registered User
    Join Date
    03-25-2015
    Location
    Boston
    MS-Off Ver
    2016
    Posts
    6

    Re: Column headers from table into pivot table rows

    True, but when I do that, I lose the "Date Filters" capability

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Data consolidation into Data Model

    I think I don't understand.
    1st you want remove columns and 2nd you want use these columns, so.... ?

    maybe attach example excel file with some dummy data and we will know what about you are talking

    edit:
    btw. change excel version in your profile to appropriate version because in Ex2007 DataModel doesn't exist
    Last edited by sandy666; 07-27-2017 at 11:38 PM.

  7. #7
    Registered User
    Join Date
    03-25-2015
    Location
    Boston
    MS-Off Ver
    2016
    Posts
    6

    Re: Data consolidation into Data Model

    For reference I attached a dummy sheet.

    My issue is that I want to consolidate my data using the PivotTable Wizard, but the "Description" column will prevent me from enabling Date Filters. I want the "Description" column to be designated as a separate Field.
    Attached Files Attached Files

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Data consolidation into Data Model

    something like that?
    Attached Files Attached Files

+ 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. Replies: 6
    Last Post: 01-24-2017, 06:56 PM
  2. [SOLVED] Copy Pivot into Table - - headers going into Table body
    By michellepace in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-29-2016, 08:39 PM
  3. Pivot Table Data to Column Headers
    By danielex in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-05-2015, 02:39 PM
  4. Pivot table not pulling column headers
    By joyhampton in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-18-2013, 11:21 PM
  5. pivot table column headers
    By jrtaylor in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-11-2009, 09:23 AM
  6. Replies: 1
    Last Post: 03-06-2009, 12:45 PM
  7. Replies: 0
    Last Post: 10-12-2005, 11:05 AM

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