Data consolidation into Data Model

**moneguia** · 07-26-2017, 05:04 PM

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!

**sandy666** · 07-26-2017, 06:01 PM

Add your tables to Data Model then create relationship(s) between them and next create Pivot Tables as you wish

**moneguia** · 07-27-2017, 11:00 PM

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

**sandy666** · 07-27-2017, 11:12 PM

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

**moneguia** · 07-27-2017, 11:29 PM

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

**sandy666** · 07-27-2017, 11:32 PM

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

**moneguia** · 07-31-2017, 10:16 AM

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.

**sandy666** · 07-31-2017, 10:59 AM

something like that?

Data consolidation into Data Model

LinkBack

Thread Tools

Rate This Thread

Display

Data consolidation into Data Model

Re: Column headers from table into pivot table rows

Re: Column headers from table into pivot table rows

Re: Column headers from table into pivot table rows

Re: Column headers from table into pivot table rows

Re: Data consolidation into Data Model

Re: Data consolidation into Data Model

Re: Data consolidation into Data Model

Thread Information

Users Browsing this Thread

Similar Threads

Table to column problem to create dataset for pivot table [Bloomberg data to pivot table]

[SOLVED] Copy Pivot into Table - - headers going into Table body

Pivot Table Data to Column Headers

Pivot table not pulling column headers

pivot table column headers

Pivot Table Formatting - Rearrange data fields to be column headers?

refresh of pivot table clears fields (column headers)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions