+ Reply to Thread
Results 1 to 21 of 21

Incrementing based on Pivot Title (count of ____)

  1. #1
    Registered User
    Join Date
    10-26-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    50

    Question Incrementing based on Pivot Title (count of ____)

    OK this question evolved out of me trying to solve the problem discussed on this thread: http://www.mrexcel.com/forum/showthread.php?t=601266

    Then I realized that perhaps simpler than the possible solutions that arose out of that thread was to deal directly with the created pivot table and correct it to show what it truly should.

    This first table shows the resulting pivot table that occurs if the selected range does not include the header row. As you can see when this occurs it interprets the first cell in the range as the header and as a result the count of the whatever it interprets as the header is one less than it should be. This also influences the grand total count.
    1 Count of Drunk Driving
    2 Drunk Driving Total
    3 Drunk Driving 1
    4 Texting 1
    5 Aggressive Driving 1
    6 Rain 1
    7 Fog 1
    8 Fatigue 2
    9 Grand Total 7

    This second table shows what the resulting pivot table should look like.
    1 Count of Type Description
    2 Type Description Total
    3 Drunk Driving 2
    4 Texting 1
    5 Aggressive Driving 1
    6 Rain 1
    7 Fog 1
    8 Fatigue 2
    9 Grand Total 8

    Having the correct first two rows is actually not all that important (and in my final dashboard I will probably only show the user the five most frequent causes and their corresponding frequencies, and probably sum those 5 to give the grand total of the 5 most contributing causes). So what I need to figure out is how to go from first "problem" table to the second? Thank you in advance for any advice and happy hump day!

  2. #2
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Incrementing based on Pivot Title (count of ____)

    How would you determine what is the header row? You could simply use Currentregion but there may have been a good reason for the user to exclude rows above the selected data.
    Good luck.

  3. #3
    Registered User
    Join Date
    10-26-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    50

    Question Re: Incrementing based on Pivot Title (count of ____)

    The header row is always the first row on the sheet the user selects their range of interest from. This sheet's first column is the incident date while the rest of the columns have various headers relating to qualitative (and quantitative information but quantitative isn't that useful in tag clouds so I don't see the user running this code on any column of number info) incident information. The user would want to select ranges that exclude rows above the selected data any time they want to summarize incidents in the past but not necessarily the most recent (i.e. summarize incidents 2 months ago when 2 more months of data have been collected above it).

    I was thinking it would be easier than dealing with the header problem discussed in the other post by trying to correct any table that was a result of the user not including some recent data in their range selection. I guess the problem your foreseeing or at least made me think of was that it would be difficult to differentiate between a table that did include the recent data (second table shown) and a table of a selected range that only had once occurrence of the contents of the first cell of the range (interpreted as the header by pivot table). This third table looks like this:

    A B
    1 Count of Fog
    2 Fog Total
    3 Fatigue 7
    4 Aggressive Driving 4
    5 Texting 10
    6 Drunk Driving 7
    8 Grand Total 28

    I'm wondering if some check to see if cell A2's (from the pivot) contents are found in the first row of the sheet the user selected the data from and if it is found in the header row the table is correct and must have resulted from the user including the header row in their range selection.

    If A2 does not match any of the headers from the incident data sheet where the user selected the range it must either be either table 1 or table 3 from this thread. In which case if it is table 1, 1 should be added to the corresponding count of it's match (i.e. add 1 to the count of drunk driving).

    If it is table 3, there is no match of A2 within the pivot because it had been interpreted as the header AND only occurred once within the range. In this case the contents of A2 would need to be placed into the table as well as a corresponding count of 1.

    Now if I only knew how to code this, I believe the strategy is sound and the simplest approach possible (although could easily be wrong about it being the simplest approach since my programming is on par with a smart chimp)

    Thanks again for your advice and any help you can provide!
    Last edited by VTHokie11; 01-26-2012 at 11:04 AM.

  4. #4
    Registered User
    Join Date
    10-26-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Incrementing based on Pivot Title (count of ____)

    If its possible to post a workbook if that'd be easier I'd be happy to.

  5. #5
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Incrementing based on Pivot Title (count of ____)

    A sample workbook would probably make it easier.

    Do you expect the final result to still be a live pivot table?

  6. #6
    Registered User
    Join Date
    10-26-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    50

    Question Re: Incrementing based on Pivot Title (count of ____)

    Nope I do not need the pivot table to be live at all, thank you for asking that that's probably very important for coding purposes. Final result will be showing this frequency table (or more likely only the 5 most frequent, their corresponding counts, and a sum of those five counts) alongside a tag cloud which I already have working and displaying on another sheet within the workbook. It will purely just to show the user the frequency counts that are the basis of the tag cloud.

    How can I provide the workbook to the thread or should I just email it? I will need a few moments to take out some confidential information.
    Last edited by VTHokie11; 01-26-2012 at 11:30 AM.

  7. #7
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Incrementing based on Pivot Title (count of ____)

    You can attach it to a post here (Go Advanced - Manage attachments). It will be a few days before I can look at it anyway, so there is no immediate hurry.

  8. #8
    Registered User
    Join Date
    10-26-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Incrementing based on Pivot Title (count of ____)

    Great, I've attached the workbook and added a few notes within it to hopefully make it more clear what I'm trying to accomplish. The frequency table creation subroutine is contained within the "Pivot" module and labled MakePivot2 or MakeTable2 (its the second subroutine in the module). Please let me know if there's any further explanation or clarification needed.

    And most importantly thank you for your help! If your curious and want to see the jscript files to make the cloud creation macro just work let me know. Thanks again!
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-26-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Incrementing based on Pivot Title (count of ____)

    Does anyone have any advice on how to accomplish this? Please let me know if anything needs to be better explained, I'd love to be done with this little tool by the end of the week. Have some more time to try and fiddle my way through it the next couple days. Thanks again everyone, hope January was a good month for you all.

  10. #10
    Registered User
    Join Date
    10-26-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Incrementing based on Pivot Title (count of ____)

    So I've made a good amount of head way poking away at this today. Right now I'm working on trying to add one to the count of whichever cell matches the header (if any)...unfortunately this line doesn't seem to work: ThisCell2.Offset(0, 1).Value = ThisCell2.Offset(0, 1).Value + 1. I know the match is working because I can get it to print "TRUE" as a test, now I just need it to add 1 to the count given the same matching condition that prints true.

    I'll continue trying to find the correct syntax/method for this but if anyone knows that'd be hugely appreciated!!

    Please Login or Register  to view this content.

  11. #11
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Incrementing based on Pivot Title (count of ____)

    If all you want is a top 5 summary, I would not bother with the pivot table. You could use something like this
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    10-26-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    50

    Question Re: Incrementing based on Pivot Title (count of ____)

    OEGO,

    Thank you so much for your continued help on this first off. When running the code you provided I received a message box which simply said "Type Mismatch." However, even so it is creating a sheet named "FreqTable" and placing the correct header in column A and "Total" in column B.

    I've stepped through the code to try and pinpoint the issue but all I could really distinguish was that the message was generated at these last couple lines of code:

    Please Login or Register  to view this content.
    This is very exciting, and look forward to figuring this "Type Mismatch" message out and getting the rest of the code to carry through. Thanks again for your help this is great learning how to do this and wouldn't be able to do it without forum help like this.
    Last edited by VTHokie11; 02-02-2012 at 02:27 PM.

  13. #13
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Incrementing based on Pivot Title (count of ____)

    Can you comment out the second on error goto err_handle line and then rerun it and say which line is highlighted. It worked fine in my tests on your sample file.

  14. #14
    Registered User
    Join Date
    10-26-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    50

    Question Re: Incrementing based on Pivot Title (count of ____)

    I commented out the second line and had an interesting unexpected result. It did not error out but instead the selected range (B5:B25) contained the following counts (by my manual count): Drunk Driving: 7, Aggressive Driving: 2, and Texting: 12.

    However within the created frequency table on "FreqTable" sheet however there was no "Texting" or corresponding count. Only Drunk Driving and Aggressive Driving were included however their counts were correct. Thanks again for all your help OEGO!
    Last edited by VTHokie11; 02-02-2012 at 05:55 PM.

  15. #15
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Incrementing based on Pivot Title (count of ____)

    Oh - I had not thought about you having less than 5 items. Can you replace this line
    Please Login or Register  to view this content.
    with this
    Please Login or Register  to view this content.
    and then test it again, please?

  16. #16
    Registered User
    Join Date
    10-26-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Incrementing based on Pivot Title (count of ____)

    Tested it and unfortunately didn't notice any difference. It seems to be ignoring whatever is the contents of the first cell in the selected range (i.e. B5:B15 returns a count of 7 for "Drunk Driving" but does not have "Texting" within the table. Thanks again for the continued help OEGO! Also I'm looking at the code now to see how I can make the summary table display adjacent to the html browser (which displays the tag cloud).

  17. #17
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Incrementing based on Pivot Title (count of ____)

    Apologies - schoolboy error on my part. Please test this
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    10-26-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Incrementing based on Pivot Title (count of ____)

    That's awesome! Thank you so much OEGO it's working correctly.

    The last part of this puzzle is going to be incorporating this table with the tag cloud html browser display in a dashboard type presentation. At the least I would like to display this table on the "Cloud" worksheet adjacent to the tag cloud html browser.

    But I was thinking what may be even better for the user would be for the macro to 1) Create the Cloud sheet 2) Create the html browser (and size it as I have on the current "Cloud" sheet) 3) Place the summary table directly adjacent to it so the user can quickly reference the frequencies that back up the tag cloud.

    In this way the data set workbook would look the same as it always has until they run the macro which would provide them with a summary dashboard with both the visual representation (tag cloud) and the numeric (summary frequency table) of the data. The tag cloud is currently generated by running the "test" subroutine within the MrExcel Module, however it requires 3 files which I have and can provide but don't believe it'd be necessary to provide as it successfully runs as long as their is a sheet with a specific name (currently "Cloud") that has an html browser present.

    If its not possible to create and size an html browser on a sheet perhaps the macro could simply unhide a "Cloud" sheet which already has the html browser sized correctly. Thank you so much once again OEGO for helping me get to this point. If you have any advice on these next steps that would be amazing but you've already done so much! I will start trying to figure out the rest of this (although more posts may be coming in the near future). Thanks again!

  19. #19
    Registered User
    Join Date
    10-26-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Incrementing based on Pivot Title (count of ____)

    So I've had some time to attempt to do what I described in my last post. But for some reason I'm having trouble getting my macros to run with each other. I've set it up so that the macro you (OEGO) helped me with runs first and creates the sheet, frequency table, and now the web browser as well. Then it calls the macro "test" which sends the selection to the "WordCloud" macro which should print the tag cloud to the web browser (it requires a few files which are stored locally on my machine).

    I believe the call isn't working correctly as I placed a stop within the test macro and it never paused there. I also placed a message box at the end of the WordCloud macro which stated "Macro Finished," and this also never appeared. I would like to have all three run off of the selection (i.e. do away with the following lines of code:

    Please Login or Register  to view this content.
    The following is the three macros together which I am trying to get to run all at once off a selected range (i.e. highlight range and press play, with no message prompt asking for the user to select the range of interest). I thought I knew how to call a macro (although not too sure about how to pass the selection between macros).

    Please Login or Register  to view this content.

  20. #20
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Incrementing based on Pivot Title (count of ____)

    Try this (this really ought to be a new thread)
    Please Login or Register  to view this content.

  21. #21
    Registered User
    Join Date
    10-26-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    50

    Thumbs up Re: Incrementing based on Pivot Title (count of ____)

    Thanks for another response OEGO. Unfortunately that seems to yield a type mismatch. I have started a new thread per your advice: http://www.excelforum.com/excel-prog...-as-input.html

    Probably should have named the thread "Getting Subroutines to run together with same range selection as input." Or is macro and subroutine pretty much interchangeable? Anyhow new thread has been started as the original question/problem I posed here has been answered by OEGO! Thanks again!
    Last edited by VTHokie11; 02-06-2012 at 04:23 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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