Grouping by Date in a Pivot table from Multiple Consolidation

[bjkaddy]

Hi All,
I have used multiple consolidation ranges from 4 tabs to 1 pivot table that have data pulling from 4 connections. From this Multiple consolidation ranges pivot table I am trying to group a date column however when I try to do this it will not give me the option to select Days, years, quarter, etc. It will just group everything into "Group 1".
I have seen online that this could be happening do to blank cells?
Any help/workarounds would be great.

Thanks in advance,

Bjorn

 

[dearaghu]

This is Excellent Jerry
. How to move pivot table to source workbook? I could find references to move to new work sheet in the existing new workbook, couldn't find any reference to move to source workbook while preserving the formulas

 

[Jerry Sullivan]

Hi dearaghu, I'm happy that you found this helpful.

To move the PivotTable, simply select the entire PivotTable range, > Ctrl-X (for Cut) > select a destination cell in the workbook with the DataSource > Ctrl-V (for Paste).

 

[dearaghu]

Cut and paste only copying table values but not the pivot table

 

[Jerry Sullivan]

Cut and paste only copying table values but not the pivot table

Make sure to select the entire range of the Pivot Table including any Report Filters.

Also, both workbooks (source and destination) need to be in the same Excel Application (session). If you've opened them in two separate sessions of Excel, it won't work.

 

[dearaghu]

Opening in same session worked. Thanks for your help

 

[Hooly]

Query Wizard - Choose Columns:
Select the Name "MyDataA" then click the ">" button to select its fields
Next >

That's it! There are a lot of steps, but after doing it a few times it's easy to remember the process.

Hi Jerry

I used this & it worked like an absolute dream but I've just tried to set up another pivot to external data, which I'm sure I've done before. But at the stage above where I'm choosing columns from my source file no matter what I seem to do I just get an old table reference in the "Available Tables & Columns" selection area, not the new data I've just linked to in the previous step.

I'm not sure where I'm going wrong & as I said I know I've done this several times before.

Any ideas?
Cheer
Pete

 

[Jerry Sullivan]

Hi Pete,

When you say an old table reference appears, do you mean one that should no longer be in the workbook or just one that you haven't changed recently?

Regarding the missing new tables, assuming you have the correct workbook referenced, the new tables might not be showing up if they are not "Static" named ranges (Names that Refer to: a specific address) with Workbook scope.

The tables won't show up if they are:
Dynamic Named Ranges (that use formula instead of static addresses)
Excel Tables (ListObjects)
Static Named Ranges with Worksheet Scope

 

[Hooly]

Hi Jerry

It was an old reference table that shouldn't have existed in the file I was looking into & originally I couldn't see anything wrong. However checking through your comments above I saw firstly that one of the 4 ranges I'm querying wasn't set up correctly so thanks for that.

I then checked the External Data Connections & somewhere hidden in the file must have been a block from an old table that got copied in the numerous variations I've done, so I rebuilt the data from scratch again & the issue corrected & I can now see the "available tables" & select the columns for the query.

Annoyingly having put the SQL statement in "SELECT * FROM........" format for the 4 elements I now have which again which I've done successfully several times previosuly following your instructions, a "Could not add table........" error comes up after the "SQL ....... graphically" prompt. There must presumably be something else wrong in the raw data so I'll have to review again. Ho hum!!

Thanks for your assistance it was much appreciated.
Pete

 

[asad]

Thanks a lot Jerry.
This method saved me ton of time. I was having hard time trying to get results from about 2.5 million rows of data. This method of yours helped me big time.

Thanks
Asad

 

[Jerry Sullivan]

Hi Asad, Thanks for your feedback.

The more recent versions of Excel include Get & Transform features (Power Query), which can do the same task with a more user-friendly interface.