+ Reply to Thread
Results 1 to 27 of 27

Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

  1. #1
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    230

    Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    Experts:

    I need some assistance with creating slicers and pivot table(s) + associated interactive/dynamic graph(s).

    Background:
    I have some (sample) data -- see attached XLS -- which includes the following data elements:

    - 3 Units (USS ABC, USS KLM, USS XYZ) + associated info in columns B and C
    - 2 Home Ports (Norfolk, San Diego) across column D
    - Year and Month values (columns E and F)
    - Categories (Alpha and Bravo) across columns G:J and K:N
    - Factors Alpha-1:4 and Factors Bravo-1:4
    - Various (random) values in cell range G3:N20

    What I Need Some Help With:
    - For this data set, one could create multiple graphs to visualize depict the information. As indicated, this is only sample data but my actual data set is much more extensive.

    - For instance, the various graphs could include the following:
    1. Graph (histogram or pie chart) for "Units" and their associated factor values
    2. Graph for "Home Ports" and the # of associated unit counts
    3. Graph for "Class Type" and "Factor Count"
    4. Graph by Dates (Year/Month)
    etc. etc.

    - Now, instead of having 5 or 6 or even more (distinct) graphs, I'd like to minimize the # of graphs but dynamically display as much information as possible. For instance, I'd like to use maybe "Pivot Tables" and "Slicers" in order to having both an interactive data selection and dynamic graph.

    My question: Based on the provided sample data, could someone please assist me with setting up the "Slicer", "Pivot Table" and "Graph" which will allow for interactive/dynamic visualization of the data set?

    Thank you in advance for any assistance in this matter.

    EEH
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    73

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    1 Select Home Port's header and data then select create pivot table (if your data was in a table you would not need to keep updating the range when you data is added)
    - In PivotTable Analyze select insert slicer

    2 Create pivots for the data you want to show, then Insert chart by selecting the one you want
    -Using example 3 the pivot would need the fields 'Class type' and your 'factor counts'

    3 Select slicer and in the ribbon select 'Report Connections'
    -Select the pivot tables you want to connect to the slicer, which will most likely be all
    -If filtered on "Norfolk" the graphs will only show Norfolk data

    Same process applies for creating a slicer on 'unit' for example.

    If you need help with creating the actual graphs there's lots of free articles online.

    I tend to have a Dashboard page for users and a hidden one with all the helper tables.
    If you like my answer please *Add Reputation

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    To get you started. See the attached Pivot Table (PT)
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    230

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    oeldere -- thank you... I appreciate the sample pivot tables.

    My question(s):
    1. Is table on tab "Output" the input for table on tab "PT"?
    2. That is, do I need a "helper" table to create various pivot tables?

    Thank you!

    EEH

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    Yes that is the input of the pivot table.

    I used the code below for it.

    The code is also available in the posted file.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    73

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    Hi

    Sorry I should have attached a sample. Iíll do it tomorrow when I get to my laptop unless this solution works for you.

  7. #7
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    230

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    MushroomFace - thank you for the input. Yes, I'd love to see your example. Ideally, I'd like to stay away from code. I personally don't mind but I will have to pass the solution to non-technical users who will have to modify/tweak the solution. So, the less code the better off they'll be.

    Again, looking fwd to seeing your solution tomorrow.

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    You can run the code with an (added) button in your sheet. Even non-technical users can learn to push a button.

  9. #9
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    73

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    Hi

    Looking at the attachments, Oeldere's method is perfect, as a finance person data is so much easier to work with in a tabular format, so each line is a transaction and there is one column for values. It makes it easier with filtering and manipulating.

    It would make sense to work with Oeldere's version with a macro button.

    For more ideas, in my attachment:

    Method 1 ; works if your data is already in a useable format, can you see you can only bring one sum column in.
    Method 2 ; puts data into the tabular format using Power Query (PQ), this contains code too but see notes tab for how i have done it, to see if you understand it over VBA? This is the same logic as your earlier example but different method.

    Not the prettiest examples just something i quickly pulled together.

    Note, if it was to break, i think there are more VBA users than PQ. I hardly meet PQ users at work just on the internet!

    When i first started building i searched for things like 'Building your own dashboards in excel', some great free tutorials online.

    Good luck
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    230

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    oeldere -- thanks for the additional information. Yes, I definitely will add a command button to execute the VBA. Given this was sample data (and the actual data may grow over time), the customers need to be able to tweak the code.

    For instance, there may be two types of modifications I need to be familiar with:
    1. Another column is added to the "Unit Information) section. For example, I may want to insert "State" between existing columns D and E (so, "Month" will be in column G).
    - Based on that column insert, I will modify the current column G:N accordingly.

    2. More importantly though, what if another another factor (e.g., "Factor Bravo-5) need to to be added.
    - Assuming I have NOT added "State" yet, and I want to add "Factor Bravo-5 into column O, what code modifications need to be made?

    Specifically, I can follow the logic of the code, but I'm not entirely clear on the following lines of code:

    col = 7
    For i = 3 To rsht1
    rsht2 = rsht2 + 1

    col = col + 1
    Loop col = 7


    So, if I were to insert only new column O "Factor Bravo-5", what would the changed code look like? Also, besides modifying the column references, what is required to change once I also add "State" and push all subsequent columns to the right?

    Thanks,
    Tom

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    col = 7 => column G => this is where the factor data will start
    So if you add a column you have to change col = 7 in col = 8
    and change Loop col = 7 in Loop col = 8


    For i = 3 => start at row 3


    Do While .Cells(2, col).Value <> "" => look in row 2 and use all columns in row 2
    (so if you add data directly after the last column in row 2, this will also be added in the output sheet)


    If you add a new column to the data (before the factor data) the code below

    Please Login or Register  to view this content.
    needs to be changed in:

    Please Login or Register  to view this content.
    And

    Please Login or Register  to view this content.
    in

    Please Login or Register  to view this content.
    Hope I explained well enough.

  12. #12
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    230

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    oeldere:

    Thank you for the continued assistance... 'very much appreciated.

    For starters, I have only added another column for unit information. I updated the information as you suggested. For the last line of code, however, I chose the following... i.e., since I inserted a column I increased "H" to "I".

    .Range("A1:I1").Value = Array("Unit", "Hull No.(if vessel)", "Class/Type", "Home Port", "State", "Year", "Month", "Group", "value")

    While the code executes, the output no longer looks like a table though. See attachment. Did you do the table formatting manually?
    Attached Images Attached Images

  13. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    Yes, I made the table manualy after running the code.

    After you run the code make a table of the data.

    Insert => table.

  14. #14
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    230

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    oeldere - I apologize for not getting back w/ you earlier.

    Based on your detailed instructions on how to modify the VBA, any inserts in the **Unit* section + new 2 factors are generated in the Output tab. GREAT!

    So, at this time, my unit info goes from columns A:G and my factors go from H:Q. Also, for testing purposes, I changes all values in H2:Q20.

    Now, I review the tab "PT". Given that the value changed, I right-clicked on the pivot table to refresh the data set. At this time, the refresh action results in the following error: "Reference isn't valid." How/where do I modify the pivot table range given that I have 1 additional unit column and 2 additional factor columns?

    Thank you,
    Tom

  15. #15
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    I think the link with the pivot table is broken.

    Make a new pivot table of the new data.

  16. #16
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    230

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    oeldere:

    Thank you... again, I appreciate your help. I think I'm making some good progress with setting up the framework for the non-technical team.

    I certainly don't want to abuse your help, but with your permission, I'd like to ask you a follow up question. Allow me to recap first:

    - Per the XLS that I posted in the original thread, the "Unit Information" covers columns A:F.
    - Then I have 4 factors which I called "Alpha" factors. Alpha factors cover columns G:J.
    - Finally, I have 4 factors which I called "Bravo" factors. Bravo factors cover columns K:N.
    - Once the "Output" tab is generated (columns H:I) list all of the 8 factors and their associated values. So far so good!

    Now, the XLS included only example data. My real data set is much more extensive and depending on the category (i.e., either Alpha or Bravo), I may have 15 factors or 20 factors vs. 4 factors. So, here's my question:

    If possible, how could I modify the code so that I can utilize, e.g., multiple "Output" procedures. For instance, what if I wanted to output only the "Unit" info + the "Bravo" factors but not the "Alpha" factors? So, how would the VBA have to be tweaked so that I start in column "K" and finish with column "N". The "finish" is important as I may also have a "Charlie" category, so I need to be able to identify start & finish of the factor columns.

    I hope this makes sense. Again, I appreciate your help and your code you've already provided is extremely helpful!

    Cheers,
    Tom

  17. #17
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    You can achieve this with this code.

    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    230

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    oeldere:

    Is there any other way to NOT use the value in the cell? For instance, the customer is using a relatively long string for the factor label. For instance, in some cases, the string length is 100+ characters. I personally would prefer more concise factor names but that's what we are given.

    So, is there a way I simply could refer K2:N2? If not, is there another option that would allow for the same output but won't require the values of the factor labels?

    Thank you!

    Tom

  19. #19
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    I am sure that can be done with VBA, but I am not a VBA expert. I can't help you enough with that.

    1) But why would you like to exclude factors to get in the new format?

    Analysing in the new format makes it a lot easier.

    So if you want to analyse a new factor you have to change the code again.

    1a) How much time does the code use to re-arange the format of the data on your original data?

    1b) How often to you have to run the code?
    Last edited by oeldere; 06-25-2020 at 01:50 AM.

  20. #20
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    230

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    oeldere -- thank you for the additional feedback.

    Instead of excluding factors, my first thought to duplicate the function CONVERTROWSTOCOL and turn them into, e.g., CONVERTROWSTOCOL_Alpha or CONVERTROWSTOCOL_Bravo functions. Each would output their data then into separate tabs such as Output_Alpha or Output_Bravo. But, finally, the pivot tables & graphs would be linked to the separate tabs so the users shouldn't see a difference.

    If you believe there could be an underlying "business" issue w/ this concept, I'd welcome to hear your thoughts. Thanks again!
    Last edited by skydivetom; 06-25-2020 at 05:44 AM.

  21. #21
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    Unless the data is confidential to other users, I see no advantace in that approach.

    If you want it the way you suggest, you will need differant VBA code.

    I can post in the watercooler to get (the attention) help with this from other forummembers.

    I will wait on your reply before posting it in the watercooler.

  22. #22
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    230

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    oeldere:

    I have now fully integrated your solution into our process. Overall, it works like a charm. Also, I added to the code so that the data on the output tab will automatically formatted as a table once I execute the macro. So far so good.

    However, in order to make this tool even stronger, I think it would be great if I could separate the factors... here's why:
    1. In my example, I included 2 categories (Alpha & Bravo)... each of them included 4 factors.
    2. Also, based on the example I included the category prefix of "Alpha" and "Bravo".
    3. Now, in the actual example, I have 4 categories and a total of 50 factors.
    4. Based on #2, however, the factors (cell values) are not prefixed with a category name. Therefore, once I apply a pivot table and link a slicer to it, all 50 factors are displayed in ASC order.
    5. So, based on #4, I'd love to mimic your output code 4 times (one for each category). So, I'd end up with 4 output tables (hidden to from view) and I could link 4 pivot tables and graphs to the data. Doing so (I think) would allow me to not merge all factors in 4 long pivot table.

    Does that make sense? If yes, I surely would welcome if you could post the help/attention in the water cooler. THOUSAND THANKS FOR EVERYTHING YOU'VE DONE SO FAR!!!!

  23. #23
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    Glad I could help.

    But would you also answer the questions above.


    1a) How much time does the code use to re-arange the format of the data on your original data?

    1b) How often to you have to run the code?


    After you answered those questions, I will post it in the water cooler

  24. #24
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    230

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    I hope I'm understanding question 1a correctly... anyhow, here's my response.

    1. As of now, I have 50 factors and 120 rows (6 months of data). The execution of the output table takes about ~15-20 seconds (which is awesome!)
    2. The data is updated monthly... as "stragglers" may provide some data past the deadline, I'd say we have to re-generate the output table 2-4 times/month.

    Thanks,
    Tom

  25. #25
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    Just posted in water cooler.

  26. #26
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    230

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    Never used the forum's water cooler before... does it mean you raised this question up another level and someone else might reply to this post?

    Also, just for *testing purposes* only, I am experimenting with an arbitrary range of columns. I've take the existing code and set my start column to "20" and then added four columns names.

    Here's what I noticed:
    - In the actual data, the customer uses the ALT key to force multiple lines in the cells (due to the long 100+ character strings).
    - The code did NOT execute.
    - I then replaced the long string values (with ALT line spacing) and replaced them with dummy values "ABC", "DEF, "KLM", "XYZ"
    - At that point the code executed and extracted factors only for those 4 values.

    Again, thank you for posting the question in the water cooler. Not sure what type of response I should anticipate though. Anyhow, in event I won't be able to use a simple column reference (e.g. columns T:W), I may be forced to uses the method where I need to type in the label names.

    Given that it doesn't like carriage returns (forced through the ALT key), do I need to remove all carriage returns? Or is there another way where it could find a long string with carriage returns?

  27. #27
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

    Thanks for the rep.

    Glad I could help.

    Thanks for marking the question solved.
    Last edited by oeldere; 06-27-2020 at 11:05 AM.

+ 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. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  2. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  3. Replies: 3
    Last Post: 01-02-2014, 02:15 PM
  4. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  5. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  6. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  7. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 AM

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