+ Reply to Thread
Results 1 to 10 of 10

Filtering large data to build statement. Is there an easier way??? Pivot table/Slicers

  1. #1
    Registered User
    Join Date
    07-27-2012
    Location
    Sydney Aust
    MS-Off Ver
    Excel 2010
    Posts
    92

    Filtering large data to build statement. Is there an easier way??? Pivot table/Slicers

    I have attached a sample file with a few lines of data just to show what I am talking about.

    I have a large list of preset variables which the user will select with an ever reducing list to choose from which builds a statement . This statement is then pasted into another sheet.

    Currently I use a table and associated pivot table with the use of slicers to give me the result I am looking for. Kind of like a predictive text and sentence generator, except really dumb and crude but specific to my needs.

    This is how I start the routine

    Clear all slicers
    Then using the slicers, I select the state
    then select the City
    then the object
    then the action.
    As I do this the selections build my output (statement) into cell M1.

    I then run code vba to paste the value in Cell "F3" into another worksheet with a time stamp. I then reset the slicers to allow the next statement to be built.

    I guess what I would like to know if there is an easier way to do this which is just as visually pleasing as the Pivot table slicers???? Any hints on how its been done in the past? Better faster ways?

    Eventually I will have a userform rather than how it looks currently.

    Thanks in advance for the forthcoming help.

    Disclaimer: I know the list of states and cities is not complete but its just a sample set of data.

    edit: Maybe there is a VBA solution with the use of userforms that acts like pivot table slicers.
    Attached Files Attached Files
    Last edited by ratdogexcel; 02-21-2018 at 07:15 AM.

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Filtering large data to build statement. Is there an easier way??? Pivot table/Slicer

    if I understood correctly you can use only 3 slicers: State, Object & Action to get what you want

    Edit:
    I didn't check whole table so I made mistake. There are more than one city in the state so slicer "City" is needed.
    Last edited by sandy666; 02-20-2018 at 04:48 AM.

  3. #3
    Registered User
    Join Date
    07-27-2012
    Location
    Sydney Aust
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Filtering large data to build statement. Is there an easier way??? Pivot table/Slicer

    sandy666 thanks for the attempt to help find a better way of doing what I am doing.

    Surely what I'm doing is not the best way of doing this. Anyone got any other ideas???

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Filtering large data to build statement. Is there an easier way??? Pivot table/Slicer

    You can use filters instead of slicers

  5. #5
    Registered User
    Join Date
    07-27-2012
    Location
    Sydney Aust
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Filtering large data to build statement. Is there an easier way??? Pivot table/Slicer

    Slicers are a little more visually pleasing than filters.

    Filters also need an extra click.

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Filtering large data to build statement. Is there an easier way??? Pivot table/Slicer

    So, I think you should look for VBA solution

  7. #7
    Registered User
    Join Date
    07-27-2012
    Location
    Sydney Aust
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Filtering large data to build statement. Is there an easier way??? Pivot table/Slicer

    Thanks sandy666

    I have thought about that but I'm short on ideas. Maybe a mod can move this thread over to the VBA section of the forum.

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Filtering large data to build statement. Is there an easier way??? Pivot table/Slicer

    Use Report Post in your first post of thread and ask mod to move this thread to the VBA part of forum

    but I think you need to be more descriptive with your needs

  9. #9
    Registered User
    Join Date
    07-27-2012
    Location
    Sydney Aust
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Filtering large data to build statement. Is there an easier way??? Pivot table/Slicer

    thanks ive sent the report to get it moved to the VBA gurus and edited the post a little.

  10. #10
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,705

    Re: Filtering large data to build statement. Is there an easier way??? Pivot table/Slicer

    Moved from Excel General.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ 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. Connecting slicers from different data sets using table format (not by creating pivot)
    By karthikgmk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-17-2016, 12:36 PM
  2. [SOLVED] sorting date data in pivot table that has slicers connected to it
    By retzlar in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-26-2015, 05:11 AM
  3. [SOLVED] Disconnect Slicers, Change Pivot Table Source, Reconnect Slicers: a problem
    By Geoff. in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2015, 02:27 PM
  4. Replies: 9
    Last Post: 02-24-2014, 06:12 PM
  5. Is it possible for slicers in pivot table to also filter data sheet?
    By shelly2 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-22-2014, 07:08 PM
  6. Replies: 2
    Last Post: 05-24-2013, 03:58 PM
  7. Replies: 2
    Last Post: 04-06-2006, 03:45 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