Page 2 of 2 FirstFirst 12
Results 16 to 29 of 29
  1. #16
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,701
    Thanks
    60
    Thanked 89 Times in 82 Posts
    Brilliant. I don't need the SalesByQty since there aren't any in the SRBO sheet. So, I commented that out for now.
    [i might have qty eventually, but for now - no]

    When I look at the "pivot" sheet, column B, starting in row 5, they're all 0s which I suspect must be the QTY since there aren't any.
    Where in the VBA is that so that I can remove all those 0s? Am I guess correctly that those are the QTYs that aren't in the SRBO sheet?

  2. #17
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,351
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi Kevin

    Apologies for delay - I've been busy cleaning my Sierpinski carpets with my Menger sponges.

    So, with my v3 demo file, there are three sheets created, a [pivotData] sheet, a [pivot] sheet, and a [SalesByQty] sheet.

    When you look at my [pivotData] sheet, you will see how the vba has grabbed the data from the [SRBO] sheet. The column D Amount data from the [pivotData] sheet is used for the data field. So if you used the v3 code with your actual data, check the sheet [pivotData] to see what you have actually got in column D.

    The [SalesByQty] sheet in my demo file uses column [E] Qty from the sheet [pivotData] for the data source.

    If you have made manual updates to the vba code in your own file, you need to look very carefully at the v3 code in the last posted file. It is very easy to miss an important change. I would print the v3 code and review it line-by-line to double-check.

    Of course, if you could attach a small file with dummy data, it would be easier.

    zeddy
    Excel Fractal Construction Manager

  3. #18
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,701
    Thanks
    60
    Thanked 89 Times in 82 Posts
    Here's a small test file without the macro in it. Columns D, E; H, I; etc. are blank. Those were quantity and base price. But the amounts are the calculation that's important.
    Attached Files Attached Files
    Last edited by kweaver; 2018-08-12 at 15:28.

  4. #19
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,701
    Thanks
    60
    Thanked 89 Times in 82 Posts
    Ah, which stage did you use?
    Attached Images Attached Images

  5. #20
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,351
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi Kevin

    Thanks for the sample data.
    I needed that to see what was going on.

    When I processed that sample, I saw the 0's in column B starting at row5 as you reported.
    Row 4 has the Pivot table column headings for each Product.
    It turns out that the entry in column B is essentially for a "blank" Product, so that is why there are zero's for the Totals.

    My vba code has a section for "REMOVE BLANK ORDERS". I had assumed that this would prevent having a pivot column for "blank" Products - but, if you check the sheet [pivotData] you will see records that they still have blanks in Product Name column C

    I fixed this by changing the code to sort column C in descending order, rather than ascending order.
    i.e. update that code section to this:
    Code:
    '**************************************
    'REMOVE BLANK ORDERS..
    '**************************************
    [A2].Select                                 'select cell, then..
    ActiveWindow.FreezePanes = True             '..freeze windows at this location
    
    'v4:Sort by Product Name column in column C..
    '(places blanks at bottom; NOTE: blank Product Name = blank Qty and blank Amount)
    [a1].CurrentRegion.Sort _
        key1:=[c2], order1:=xlDescending, Header:=xlYes '<<v4
    that should do the trick.

    zeddy

  6. #21
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,351
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi Kevin

    Nice Menger pictures!
    The solid is level 0, then you have Level 1, Level 2, and Level 3 in your picture.

    A Menger sponge has amazing properties. Each Level increases the surface area towards infinity, while the volume of material reduces towards zero. It's all about surface area. It has lots of useful applications. Your lungs have similar properties - lots of surface area enclosed in a very small volume.

    I am currently building lots of Level 3 cubes to make a Level 4, still some way to go.
    I have many thousands of bricks to go.

    If you take a solid cube, you can slice it to get a hexagonal shape:
    rz-cube-slice.png

    If you get a Level 4 Menger, and slice it like above, you get an amazing display!

    I have an Excel spreadsheet that calculates the surface area for various levels.
    I am working on structures that multiply the surface area at a faster rate than Menger.

    zeddy

  7. #22
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,701
    Thanks
    60
    Thanked 89 Times in 82 Posts
    Zeddy, I'm getting an error on the key1 line when I enter that into the VBA. "Expected: expression"

    Clip0001.jpg
    Last edited by kweaver; 2018-08-13 at 10:22.

  8. #23
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,351
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi Kevin

    ..you can't have a comment line that immediately follows the underscore continuation line.
    [a1].CurrentRegion.Sort _
    ' commented line

    copy-and-paste this:
    [a1].CurrentRegion.Sort _
    key1:=[c2], order1:=xlDescending, Header:=xlYes '<<v4

    zeddy

  9. #24
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,701
    Thanks
    60
    Thanked 89 Times in 82 Posts
    Got it! But while it's working, it's still showing all the zeros in B5 onward.
    Last edited by kweaver; 2018-08-13 at 10:42.

  10. #25
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,351
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi Kevin

    About the zeros you are seeing.
    The way the pivot table works is that it generates a unique list of the Customers in column A of the pivot table, i.e. no duplicates, and sorted in A->Z order.
    It generates a unique list of the Product types in row5, for the columns, starting in column B, no duplicates, sorted in A>Z order.
    So, if you are seeing zeros in column B, I presume the Product ID in cell [B5] is "blank".
    It could be that this "Product code" actually contains space(s).
    You could check this by referring to the merged-source sheet [pivotData].
    Check whether there are any cells that contain spaces in the Product Name column C.

    zeddy

  11. #26
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,701
    Thanks
    60
    Thanked 89 Times in 82 Posts
    In pivotData, column C, there are spaces between "words" in the Product Name (e.g., "XLR8 Plus NO2") and there are some BLANK rows in that column as well.
    So, based on what you just said, if I have "Select Products" in B3 be ALL of them, then I'll get 0s in that column. I think I understand that.

    Where, if this is easy for you to point me, do I put "code" that would enable me to have more than just "Select Source" in A1 and B1? Can I get another option in C1 & D1?

    P.S. I was a bit worried that you went MIA again. Maybe you're still working on that carpet cleaning.

    Kevin
    Chair: Keep Zeddy Active Campaign or Reduce His Compensation

  12. #27
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,351
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi Kevin

    Went to New York. Now I'm back again.

    So, the mystery of your BLANK Products deepens.
    When I looked at your data sample, and manually did the xlUp on the [pivotData] column C, it seems that Excel believes your blank cells for Product are not blank at all.
    They get counted if you use =COUNTA(C:C)
    Yet if you use the =ISBLANK formula to test the blank cells, it says TRUE.
    And =LEN will also return 0.
    BUT Excel's End(xlUp) doesn't believe they are 'empty' cells. Weird.

    So, to remove those records on sheet [pivotData] where the Product cells appear blank, I used an Autofilter method to select and then remove them.
    See attached file.
    Try this and see if this does the job.

    When you click the Select Source page field in cell [B1], you are choosing what data will appear in the pivot report.
    When you click the Select Customers.. dropdown in cell [A4], you are choosing what rows you want to see in the report.
    When you click the Select Products.. dropdown in cell [B3], you are choosing what columns you want in the pivot report i.e. what Products you want to include in the report.

    zeddy
    My thyroid was so active they took it out.
    And my spleen.
    And other organ parts.
    Attached Files Attached Files

  13. #28
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,701
    Thanks
    60
    Thanked 89 Times in 82 Posts
    OK. I follow this...

    Where, if this is easy for you to point me, do I put "code" that would enable me to have more than just "Select Source" in A1 and B1? Can I get another option in C1 & D1?

    EDIT: I've managed to get another field in pivot!C1 (but the background color doesn't stick) and managed to get this new "field" (which is "campaign") to show up in pivotData in column E, but I cannot get the drop-down to be in pivot!D1 despite various lame attempts to modify to the VBA code.
    Last edited by kweaver; 2018-08-15 at 19:19.

  14. #29
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,351
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi Kevin

    I assume you want vba to create two page fields???
    see attached file for some ideas.

    I've added some code to show you how to change pivot table formatting e.g. cell background colors.
    When changing colors, I prefer to use vba names like
    rgbMediumAquamarine
    rgbLightGoldenrodYellow
    rgbSpringGreen
    rgbYellow
    ..rather than the unhelpful RGB(x,y,z) method.

    I have added a sheet [rz colours] to show you what you can use.

    Let us know if this is of any help

    zeddy
    Attached Files Attached Files

  15. The Following User Says Thank You to zeddy For This Useful Post:

    kweaver (2018-08-16)

Page 2 of 2 FirstFirst 12

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •