Pivot table can take a list separated by hours

Hello Everyone, I already joined to this Forum, in my Job I inherit a VBA macro that create a Pivot table from a big list, this list is separated by date and more important by hour, then many lines the same date even the same hour cause register many events in the same hour, the macro want to create a table by months and use the entire information but appearing is more than can be handle showing a message like this : " You cannot place a field that has more than 8000 items in the column area. If you want to use this field in the report, click OK, and then move the field to either the row area or page area"
I review the main page where the code takes the info and I notice take more columns more that it needs, but way more... a lot more. I tried to change the code to fix that but I could not.
Please help. Let me know if need more details or part of the code .
Thanks

Please Login or Register  to view this content.

Without seeing the data, I can only speculate on an answer.

One way might be to group the data by whatever time period you want (Day, Month).

The other would be to add helper column to the data such as =Int(A2) where Column A contains the individual record's time. This formula will "round" the day to a whole day. It will discard the time part.

If you truly have Office 365 as your profile indicates, then you have over a million rows with which to work.

I highly suggest the following:

1. Convert the data source into an Excel Table. The main reasons for this are:
- Tables know how big they are. So when you change the number of rows in the table, the pivot table will keep up dynamically.
- Tables "remember" formulas and copy them down automatically as data are added.

Here is more information on tables: http://www.utteraccess.com/wiki/Tables_in_Excel - tables are very powerful so it is worthwhile getting to know how to use them. Another advantage of tables is that intellisense works well with them. If you select a cell in the table while composing formula it will insert the header name in like so: [@[Date]] which indicates the value in the Date Column on that row. Conversely, if you type the open bracket "[" it will suggest column headers you can use.

[@[Date]] means a single cell, [Date] means the whole column that contains the dates excluding the header row and a total row it there is one. In other words, EXACTLY the data you want, no more, no less.

2. Make three helper columns in your table. Assuming that the column that contains the date is called DATE
= INT([@[Date]] this will "remove" the hours from the date, so you can gather data by whole days.
= YEAR([@[Date]] this gets the year which is a handy filter to have.
= TEXT([@[Date]],"mmm") this gets the three-letter month abbreviation; another handy filter to have.

3. Do a web search on Slicers. Slicers are fancy filters that are both very powerful, but also make your reports look a lot more professional. They are easy to implement and very intuitive.