+ Reply to Thread
Results 1 to 32 of 32

Pivot Table is rubbish because of 256 limitation?

  1. #1
    Forum Contributor HerryMarkowitz's Avatar
    Join Date
    09-10-2012
    Location
    Istanbul / Turkey
    MS-Off Ver
    2013 - Win10 - 64bit
    Posts
    1,007

    Pivot Table is rubbish because of 256 limitation?

    Hi beautiful people,
    As you can see following link, a pivot table capacity for value fields is 256.
    https://support.office.com/en-za/art...7-269d656771c3

    I want to make a pivot table for 1 year (365 days) but I dont because of 256 limitation.

    If I am not able to make a pivot table for 1 year (365 days) then Pivot Table is rubbish...

    Any idea about that?

    Note1:
    As far as I understand Power Pivot Add-In has also 256 limitations...
    So Power Pivot Add-In is also rubbish...
    https://support.office.com/en-ca/art...7-40896795d045

    Note2:
    I am wondering if I can use vba with Power Pivot Add-In?
    Last edited by HerryMarkowitz; 07-16-2015 at 04:26 AM.
    Sub DontForgetThese()
         If Your thread includes any code Then Please use code tags...
         If Your thread has been solved Then Please mark as solved...
         If Anybody has helped to you Then Please add reputation...
    End Sub

  2. #2
    Registered User
    Join Date
    07-15-2015
    Location
    South East England
    MS-Off Ver
    2010 & 2013
    Posts
    72

    Re: Pivot Table is rubbish because of 256 limitation?

    Why not give Power Pivot a try?

    https://support.office.com/en-ca/art...7-40896795d045

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: Pivot Table is rubbish because of 256 limitation?

    You can, it sounds like your source data isn't set up correctly. Post a sample workbook

  4. #4
    Forum Contributor HerryMarkowitz's Avatar
    Join Date
    09-10-2012
    Location
    Istanbul / Turkey
    MS-Off Ver
    2013 - Win10 - 64bit
    Posts
    1,007

    Re: Pivot Table is rubbish because of 256 limitation?

    Quote Originally Posted by Kyle123 View Post
    You can, it sounds like your source data isn't set up correctly. Post a sample workbook
    Hi Kyle,
    I am posting a sample workbook.
    Thanks in advance.
    Attached Files Attached Files

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: Pivot Table is rubbish because of 256 limitation?

    Your data isn't suitable for a pivot table. You've created what's typically referred to as a cross tab report.

    For data to be pivotable, it needs to be in the same layout as a database table, so you don't add columns to data, you add rows. Your table only needs 6 Columns and needs to be laid out as follows:
    Please Login or Register  to view this content.
    You then only need 6 Value fields, rather than one for everyday.

    I've attached a workbook with your data as an example
    Attached Files Attached Files

  6. #6
    Forum Contributor HerryMarkowitz's Avatar
    Join Date
    09-10-2012
    Location
    Istanbul / Turkey
    MS-Off Ver
    2013 - Win10 - 64bit
    Posts
    1,007

    Re: Pivot Table is rubbish because of 256 limitation?

    Hi Kyle,
    Thanks for your advice.
    But it is not possible to change our data as you explained.
    So, do you have another opinion or power pivot idea?
    Thanks in advance.

  7. #7
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: Pivot Table is rubbish because of 256 limitation?

    No, you can't use Pivot Tables/Power Pivot because your data is laid out poorly.

    Maybe you could use SumIfs to get what you're after?

  8. #8
    Forum Contributor HerryMarkowitz's Avatar
    Join Date
    09-10-2012
    Location
    Istanbul / Turkey
    MS-Off Ver
    2013 - Win10 - 64bit
    Posts
    1,007

    Re: Pivot Table is rubbish because of 256 limitation?

    Quote Originally Posted by Kyle123 View Post
    Maybe you could use SumIfs to get what you're after?
    Sorry, my English is not perfect. What do you mean with this?

  9. #9
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,122

    Re: Pivot Table is rubbish because of 256 limitation?

    Quote Originally Posted by HerryMarkowitz View Post
    Hi Kyle,
    Thanks for your advice.
    But it is not possible to change our data as you explained.
    So, do you have another opinion or power pivot idea?
    Thanks in advance.
    Where are you getting your data from? Are you manually capturing it or is it from some back office system.
    Many systems allow output in different layouts. Have you checked to see you can't get a row by row daily record of your four basic fileds rather than a column by column daily record.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  10. #10
    Forum Contributor HerryMarkowitz's Avatar
    Join Date
    09-10-2012
    Location
    Istanbul / Turkey
    MS-Off Ver
    2013 - Win10 - 64bit
    Posts
    1,007

    Re: Pivot Table is rubbish because of 256 limitation?

    Quote Originally Posted by Richard Buttrey View Post
    Where are you getting your data from? Are you manually capturing it or is it from some back office system.
    Many systems allow output in different layouts. Have you checked to see you can't get a row by row daily record of your four basic fileds rather than a column by column daily record.
    We dont get that data from database...
    It is manually capturing data...

  11. #11
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,122

    Re: Pivot Table is rubbish because of 256 limitation?

    Quote Originally Posted by HerryMarkowitz View Post
    We dont get that data from database...
    It is manually capturing data...
    In that case the answer is simple and in your own hands.
    So capture it as Kyle suggested earlier. Don't make life difficult for yourself with a non standard layout.

  12. #12
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: Pivot Table is rubbish because of 256 limitation?

    What Richard said ^^ though to be honest - I can't actually see what value you're getting from the pivot table. Is it the grouping? If so, have a look at the Row grouping in Excel

  13. #13
    Forum Contributor HerryMarkowitz's Avatar
    Join Date
    09-10-2012
    Location
    Istanbul / Turkey
    MS-Off Ver
    2013 - Win10 - 64bit
    Posts
    1,007

    Re: Pivot Table is rubbish because of 256 limitation?

    Quote Originally Posted by Kyle123 View Post
    What Richard said ^^ though to be honest - I can't actually see what value you're getting from the pivot table. Is it the grouping? If so, have a look at the Row grouping in Excel
    Yes, it is grouping actually but I need good viewed presentation with pivot table coloured properties

  14. #14
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: Pivot Table is rubbish because of 256 limitation?

    Then have a look at row grouping and format your data as a table. That's about the best you're going to get with your choice of layout

  15. #15
    Forum Contributor HerryMarkowitz's Avatar
    Join Date
    09-10-2012
    Location
    Istanbul / Turkey
    MS-Off Ver
    2013 - Win10 - 64bit
    Posts
    1,007

    Re: Pivot Table is rubbish because of 256 limitation?

    Quote Originally Posted by Kyle123 View Post
    Then have a look at row grouping and format your data as a table. That's about the best you're going to get with your choice of layout
    But thats need also to get subtotal every group...
    So, needs sorting etc.
    This is not easy job

  16. #16
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: Pivot Table is rubbish because of 256 limitation?

    Then you should have chosen a better layout for your data

    If you have a search for "cross tab" to line items or Pivot data you'll find a lot of code that will format your data on a separate sheet properly (cross tab is the general term for the type of layout you have)

  17. #17
    Forum Contributor HerryMarkowitz's Avatar
    Join Date
    09-10-2012
    Location
    Istanbul / Turkey
    MS-Off Ver
    2013 - Win10 - 64bit
    Posts
    1,007

    Re: Pivot Table is rubbish because of 256 limitation?

    Quote Originally Posted by Kyle123 View Post
    Then you should have chosen a better layout for your data

    If you have a search for "cross tab" to line items or Pivot data you'll find a lot of code that will format your data on a separate sheet properly (cross tab is the general term for the type of layout you have)
    Hi Kyle,
    I found some code in the internet but all of them very complicated for me.
    So, I need a simple way...
    Last edited by HerryMarkowitz; 07-17-2015 at 09:47 AM.

  18. #18
    Forum Contributor HerryMarkowitz's Avatar
    Join Date
    09-10-2012
    Location
    Istanbul / Turkey
    MS-Off Ver
    2013 - Win10 - 64bit
    Posts
    1,007

    Re: Pivot Table is rubbish because of 256 limitation?

    Hi Kyle,
    I found very good video regarding this issue.
    https://www.youtube.com/watch?t=157&v=pUXJLzqlEPk
    As you can see in that video, you can convert cross table to flatten list using pivot table wizard.(There is no need vba code)

    But I need your support here because my cross table includes 4 rows headings not 1 as in the video explained.
    Please check following picture.
    http://i.cubeupload.com/Qm3npC.png

    Thanks in advance.

  19. #19
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: Pivot Table is rubbish because of 256 limitation?

    Do what they've done - just with one column, then insert 3 blank columns and use VLookup to populate them - you can then pivot the result

  20. #20
    Forum Contributor HerryMarkowitz's Avatar
    Join Date
    09-10-2012
    Location
    Istanbul / Turkey
    MS-Off Ver
    2013 - Win10 - 64bit
    Posts
    1,007

    Re: Pivot Table is rubbish because of 256 limitation?

    Quote Originally Posted by Kyle123 View Post
    Do what they've done - just with one column, then insert 3 blank columns and use VLookup to populate them - you can then pivot the result
    Vlookup doesnt work because vlookup brings first found value...

  21. #21
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: Pivot Table is rubbish because of 256 limitation?

    So move the columns around...

  22. #22
    Forum Contributor HerryMarkowitz's Avatar
    Join Date
    09-10-2012
    Location
    Istanbul / Turkey
    MS-Off Ver
    2013 - Win10 - 64bit
    Posts
    1,007

    Re: Pivot Table is rubbish because of 256 limitation?

    Quote Originally Posted by Kyle123 View Post
    So move the columns around...
    If I cut three colums and paste to near to last column then that three colums will be middle data not row headings?

  23. #23
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: Pivot Table is rubbish because of 256 limitation?

    Come on, use a bit of grey matter, they'll be at the end, just don't include them in your consolidation range. VLookup them in after you have properly formatted data

  24. #24
    Forum Contributor HerryMarkowitz's Avatar
    Join Date
    09-10-2012
    Location
    Istanbul / Turkey
    MS-Off Ver
    2013 - Win10 - 64bit
    Posts
    1,007

    Re: Pivot Table is rubbish because of 256 limitation?

    Yes, you are right Yep, solved...
    Although I prefer without using vlookup...to make simple...

  25. #25
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: Pivot Table is rubbish because of 256 limitation?

    Well it's tough, you're going to have to

    And VLookup is simple!

  26. #26
    Forum Contributor HerryMarkowitz's Avatar
    Join Date
    09-10-2012
    Location
    Istanbul / Turkey
    MS-Off Ver
    2013 - Win10 - 64bit
    Posts
    1,007

    Re: Pivot Table is rubbish because of 256 limitation?

    Quote Originally Posted by Kyle123 View Post
    And VLookup is simple!
    You know what flatten table rows count is 44000.
    That means I must wait a long time to complete vlookup action.
    And I must do this for 3 columns.
    So, I dont agree with you that vlookup is the good way

  27. #27
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: Pivot Table is rubbish because of 256 limitation?

    There isn't a better way - you do it once and then copy and paste values - you've spent longer looking for an alternative!

  28. #28
    Forum Contributor HerryMarkowitz's Avatar
    Join Date
    09-10-2012
    Location
    Istanbul / Turkey
    MS-Off Ver
    2013 - Win10 - 64bit
    Posts
    1,007

    Re: Pivot Table is rubbish because of 256 limitation?

    Quote Originally Posted by Kyle123 View Post
    There isn't a better way - you do it once and then copy and paste values - you've spent longer looking for an alternative!
    As alternative way I found a code from here...
    http://nhsexcel.com/excel-pivot-tabl...tab-flat-list/
    But I think when I integrated that code to my file it also take a long time to complete because of for next loop...

  29. #29
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: Pivot Table is rubbish because of 256 limitation?

    That's a terrible way in comparison - use vlookup, much faster ;P

  30. #30
    Forum Contributor HerryMarkowitz's Avatar
    Join Date
    09-10-2012
    Location
    Istanbul / Turkey
    MS-Off Ver
    2013 - Win10 - 64bit
    Posts
    1,007

    Re: Pivot Table is rubbish because of 256 limitation?

    Of course we can use fast vlookup for this
    https://fastexcel.wordpress.com/2012...han-1-vlookup/

  31. #31
    Registered User
    Join Date
    08-28-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Pivot Table is rubbish because of 256 limitation?

    Try this....

    Begin creating the Pivot and drop the dates in rows and names in Col... hoping this worked fine.
    then insert a pivot chart on it by going to Analyze/ Pivot Chart
    then select chart/ goto Design/ Select Data/ Switch row/column
    this will give you the pivot format you were looking for with grouped dates
    now you can delete the chart and have your pivot.

    let me know if it worked??

  32. #32
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,903

    Re: Pivot Table is rubbish because of 256 limitation?

    navniit, perhaps you missed that this thread is almost 5 years old?
    (dont worry, I have done the same thing myself, before)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Load 0f Rubbish!!!
    By AliGW in forum The Water Cooler
    Replies: 31
    Last Post: 03-10-2014, 09:56 AM
  2. Rubbish HTML
    By Kyle123 in forum Suggestions for Improvement
    Replies: 2
    Last Post: 09-14-2012, 07:24 AM
  3. Replies: 1
    Last Post: 02-08-2012, 09:48 AM
  4. Pivot Table - Data Limitation
    By Garya in forum Excel General
    Replies: 3
    Last Post: 12-09-2008, 04:30 AM
  5. [SOLVED] Excel pivot table limitation when having Access
    By Joël in forum Excel General
    Replies: 2
    Last Post: 01-27-2006, 04:45 AM
  6. [SOLVED] Make Table Query limitation
    By quartz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-30-2005, 09:05 AM
  7. [SOLVED] Pivot table - Charachter limitation
    By Darin Kramer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-24-2005, 06:05 PM
  8. pivot table limitation
    By tchangmian in forum Excel General
    Replies: 1
    Last Post: 03-17-2005, 11:06 PM

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