Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Pivot Table to Display Filtered Data Only
OK, I'm trying to do the following:
I have a spreadsheet with the sample columns: Region | Country | Cust_Seg | Classification | twelve more columns Since Excel won't let me filter on more than two items using auto filter, I'm trying to utilize a pivot table (so I was told) to display the data I want. In short, I want the user to be able to select 0, 1, or many (even all) Regions and or Countries and or Cust_Segs and or Classifications (all the combinations) and display the other twelve fields as well with their related data. If I could use the auto-filter to allow them to pick/filter more than two items using the "custom" feature, then this would not be a problem. Also, I don't need to see any totals, counts, products, etc for the final results. I'm not even sure a pivot table is the answer (I don't think it is), but I've been directed twice by coworkers to consider "the pivot table". While they are great for summary information, totals and the likes, I don't think they're going to give me the answer I want. Are they?? Pivotal Decision Awaiting on Pivot Tables, JCH -- Dawg House Inc. "We live in it, therefore, we know it!" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Pivot Table to Display Filtered Data Only
If I want to filter on multiple values in a field (or even multiple values in
multiple fields), I sometimes create a formula in a helper column and filter by that. I can make that formula as complex or as simple as I want. I could even use that helper column as a page field in the pivottable. Ron de Bruin has an alternative: http://www.rondebruin.nl/easyfilter.htm Dawg House Inc wrote: OK, I'm trying to do the following: I have a spreadsheet with the sample columns: Region | Country | Cust_Seg | Classification | twelve more columns Since Excel won't let me filter on more than two items using auto filter, I'm trying to utilize a pivot table (so I was told) to display the data I want. In short, I want the user to be able to select 0, 1, or many (even all) Regions and or Countries and or Cust_Segs and or Classifications (all the combinations) and display the other twelve fields as well with their related data. If I could use the auto-filter to allow them to pick/filter more than two items using the "custom" feature, then this would not be a problem. Also, I don't need to see any totals, counts, products, etc for the final results. I'm not even sure a pivot table is the answer (I don't think it is), but I've been directed twice by coworkers to consider "the pivot table". While they are great for summary information, totals and the likes, I don't think they're going to give me the answer I want. Are they?? Pivotal Decision Awaiting on Pivot Tables, JCH -- Dawg House Inc. "We live in it, therefore, we know it!" -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Pivot Table to Display Filtered Data Only
Thanks for the suggestion Dave. I checked out the "easy filter" option, but
that's not an option for me in this case. I am trying to take the spreadsheet and have a way to present the data in an easy to use (the users will be selecting the values to filter) way that doesn't require the user to do anything but select the data elements they want (or don't want) to see - multi-selecting would be great (if an option). I'm not a pivot table person per se, so I'm not sure on the capabilities of them nor their extensive features. I simply use them for summary information. As for your "formula in a helper column", would you care to share an example of this so I can see if it meets the needs of the users ? Thanks again. Cheers, JCH -- Dawg House Inc. "We live in it, therefore, we know it!" "Dave Peterson" wrote: If I want to filter on multiple values in a field (or even multiple values in multiple fields), I sometimes create a formula in a helper column and filter by that. I can make that formula as complex or as simple as I want. I could even use that helper column as a page field in the pivottable. Ron de Bruin has an alternative: http://www.rondebruin.nl/easyfilter.htm Dawg House Inc wrote: OK, I'm trying to do the following: I have a spreadsheet with the sample columns: Region | Country | Cust_Seg | Classification | twelve more columns Since Excel won't let me filter on more than two items using auto filter, I'm trying to utilize a pivot table (so I was told) to display the data I want. In short, I want the user to be able to select 0, 1, or many (even all) Regions and or Countries and or Cust_Segs and or Classifications (all the combinations) and display the other twelve fields as well with their related data. If I could use the auto-filter to allow them to pick/filter more than two items using the "custom" feature, then this would not be a problem. Also, I don't need to see any totals, counts, products, etc for the final results. I'm not even sure a pivot table is the answer (I don't think it is), but I've been directed twice by coworkers to consider "the pivot table". While they are great for summary information, totals and the likes, I don't think they're going to give me the answer I want. Are they?? Pivotal Decision Awaiting on Pivot Tables, JCH -- Dawg House Inc. "We live in it, therefore, we know it!" -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Pivot Table to Display Filtered Data Only
If I have a list of, say, states that I want to filter by, I could put that list
in column A of another sheet. Then I could use a formula like: =isnumber(match(b2,sheet2!a:a,0)) If B2 contains the state name for that row. But you could use any formula you wanted: =or(a2="Smith",b2="mi",c21000) So I could retrieve the rows associated with Smith or Michigan or greater than 1000. It's really more of an ad hoc formula that does what it needs to do for that single purpose. Dawg House Inc wrote: Thanks for the suggestion Dave. I checked out the "easy filter" option, but that's not an option for me in this case. I am trying to take the spreadsheet and have a way to present the data in an easy to use (the users will be selecting the values to filter) way that doesn't require the user to do anything but select the data elements they want (or don't want) to see - multi-selecting would be great (if an option). I'm not a pivot table person per se, so I'm not sure on the capabilities of them nor their extensive features. I simply use them for summary information. As for your "formula in a helper column", would you care to share an example of this so I can see if it meets the needs of the users ? Thanks again. Cheers, JCH -- Dawg House Inc. "We live in it, therefore, we know it!" "Dave Peterson" wrote: If I want to filter on multiple values in a field (or even multiple values in multiple fields), I sometimes create a formula in a helper column and filter by that. I can make that formula as complex or as simple as I want. I could even use that helper column as a page field in the pivottable. Ron de Bruin has an alternative: http://www.rondebruin.nl/easyfilter.htm Dawg House Inc wrote: OK, I'm trying to do the following: I have a spreadsheet with the sample columns: Region | Country | Cust_Seg | Classification | twelve more columns Since Excel won't let me filter on more than two items using auto filter, I'm trying to utilize a pivot table (so I was told) to display the data I want. In short, I want the user to be able to select 0, 1, or many (even all) Regions and or Countries and or Cust_Segs and or Classifications (all the combinations) and display the other twelve fields as well with their related data. If I could use the auto-filter to allow them to pick/filter more than two items using the "custom" feature, then this would not be a problem. Also, I don't need to see any totals, counts, products, etc for the final results. I'm not even sure a pivot table is the answer (I don't think it is), but I've been directed twice by coworkers to consider "the pivot table". While they are great for summary information, totals and the likes, I don't think they're going to give me the answer I want. Are they?? Pivotal Decision Awaiting on Pivot Tables, JCH -- Dawg House Inc. "We live in it, therefore, we know it!" -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Pivot Table to Display Filtered Data Only
Thanks again Dave. I don't think that's going to work for this situation as I
basically want to have them select the "filter values" from a list. In a perfect situation, they'd select the values from a list box and the values would get filtered as the values were selected in the list box. I'm all about writing code...just not sure if its possible to pass in the values for the filter. ColA | ColB | ColC ------------------------------ Ctry1 | Val1 | Code1 Ctry2 | Val1 | Code2 Ctry3 | Val2 | Code2 .... Ctry99| Val15 | Code1 ================= Basically, I'd like the Col A values to be available in a single multi-select list that would impact the view of the filtered area of the spreadsheet. Make sense? -- Dawg House Inc. "We live in it, therefore, we know it!" "Dave Peterson" wrote: If I have a list of, say, states that I want to filter by, I could put that list in column A of another sheet. Then I could use a formula like: =isnumber(match(b2,sheet2!a:a,0)) If B2 contains the state name for that row. But you could use any formula you wanted: =or(a2="Smith",b2="mi",c21000) So I could retrieve the rows associated with Smith or Michigan or greater than 1000. It's really more of an ad hoc formula that does what it needs to do for that single purpose. Dawg House Inc wrote: Thanks for the suggestion Dave. I checked out the "easy filter" option, but that's not an option for me in this case. I am trying to take the spreadsheet and have a way to present the data in an easy to use (the users will be selecting the values to filter) way that doesn't require the user to do anything but select the data elements they want (or don't want) to see - multi-selecting would be great (if an option). I'm not a pivot table person per se, so I'm not sure on the capabilities of them nor their extensive features. I simply use them for summary information. As for your "formula in a helper column", would you care to share an example of this so I can see if it meets the needs of the users ? Thanks again. Cheers, JCH -- Dawg House Inc. "We live in it, therefore, we know it!" "Dave Peterson" wrote: If I want to filter on multiple values in a field (or even multiple values in multiple fields), I sometimes create a formula in a helper column and filter by that. I can make that formula as complex or as simple as I want. I could even use that helper column as a page field in the pivottable. Ron de Bruin has an alternative: http://www.rondebruin.nl/easyfilter.htm Dawg House Inc wrote: OK, I'm trying to do the following: I have a spreadsheet with the sample columns: Region | Country | Cust_Seg | Classification | twelve more columns Since Excel won't let me filter on more than two items using auto filter, I'm trying to utilize a pivot table (so I was told) to display the data I want. In short, I want the user to be able to select 0, 1, or many (even all) Regions and or Countries and or Cust_Segs and or Classifications (all the combinations) and display the other twelve fields as well with their related data. If I could use the auto-filter to allow them to pick/filter more than two items using the "custom" feature, then this would not be a problem. Also, I don't need to see any totals, counts, products, etc for the final results. I'm not even sure a pivot table is the answer (I don't think it is), but I've been directed twice by coworkers to consider "the pivot table". While they are great for summary information, totals and the likes, I don't think they're going to give me the answer I want. Are they?? Pivotal Decision Awaiting on Pivot Tables, JCH -- Dawg House Inc. "We live in it, therefore, we know it!" -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Pivot Table to Display Filtered Data Only
Maybe you can do something like this, but it looks very similar to Ron de
Bruin's easyfilter. I added a sheet named Sheet2. I put my list of countries in B1:B25. I put a listbox from the Forms toolbar (not the control toolbox toolbar) on that sheet. I rightclicked on that listbox and selected Format Control. On the control tab, I put $b$1:$b$25 in the "input range" box I made sure that the "selection type" was Multi. Then I put a button from that Forms toolbar right next to the listbox. And assigned this macro to that button: Option Explicit Sub testme() Dim myLB As ListBox Dim iCtr As Long Dim oRow As Long With ActiveSheet Set myLB = .ListBoxes("List Box 1") .Range("a1").Resize(myLB.ListCount, 1).ClearContents oRow = 0 For iCtr = 1 To myLB.ListCount If myLB.Selected(iCtr) Then oRow = oRow + 1 .Cells(oRow, "A").Value = myLB.List(iCtr) End If Next iCtr End With End Sub And I used this in my helper column in Sheet1: =ISNUMBER(MATCH(A2,Sheet2!A:A,0)) And I could filter by that. =========== You may want to look at data|filter|advanced filter, too. Debra Dalgleish has some instructions: http://contextures.com/xladvfilter01.html Dawg House Inc wrote: Thanks again Dave. I don't think that's going to work for this situation as I basically want to have them select the "filter values" from a list. In a perfect situation, they'd select the values from a list box and the values would get filtered as the values were selected in the list box. I'm all about writing code...just not sure if its possible to pass in the values for the filter. ColA | ColB | ColC ------------------------------ Ctry1 | Val1 | Code1 Ctry2 | Val1 | Code2 Ctry3 | Val2 | Code2 ... Ctry99| Val15 | Code1 ================= Basically, I'd like the Col A values to be available in a single multi-select list that would impact the view of the filtered area of the spreadsheet. Make sense? -- Dawg House Inc. "We live in it, therefore, we know it!" "Dave Peterson" wrote: If I have a list of, say, states that I want to filter by, I could put that list in column A of another sheet. Then I could use a formula like: =isnumber(match(b2,sheet2!a:a,0)) If B2 contains the state name for that row. But you could use any formula you wanted: =or(a2="Smith",b2="mi",c21000) So I could retrieve the rows associated with Smith or Michigan or greater than 1000. It's really more of an ad hoc formula that does what it needs to do for that single purpose. Dawg House Inc wrote: Thanks for the suggestion Dave. I checked out the "easy filter" option, but that's not an option for me in this case. I am trying to take the spreadsheet and have a way to present the data in an easy to use (the users will be selecting the values to filter) way that doesn't require the user to do anything but select the data elements they want (or don't want) to see - multi-selecting would be great (if an option). I'm not a pivot table person per se, so I'm not sure on the capabilities of them nor their extensive features. I simply use them for summary information. As for your "formula in a helper column", would you care to share an example of this so I can see if it meets the needs of the users ? Thanks again. Cheers, JCH -- Dawg House Inc. "We live in it, therefore, we know it!" "Dave Peterson" wrote: If I want to filter on multiple values in a field (or even multiple values in multiple fields), I sometimes create a formula in a helper column and filter by that. I can make that formula as complex or as simple as I want. I could even use that helper column as a page field in the pivottable. Ron de Bruin has an alternative: http://www.rondebruin.nl/easyfilter.htm Dawg House Inc wrote: OK, I'm trying to do the following: I have a spreadsheet with the sample columns: Region | Country | Cust_Seg | Classification | twelve more columns Since Excel won't let me filter on more than two items using auto filter, I'm trying to utilize a pivot table (so I was told) to display the data I want. In short, I want the user to be able to select 0, 1, or many (even all) Regions and or Countries and or Cust_Segs and or Classifications (all the combinations) and display the other twelve fields as well with their related data. If I could use the auto-filter to allow them to pick/filter more than two items using the "custom" feature, then this would not be a problem. Also, I don't need to see any totals, counts, products, etc for the final results. I'm not even sure a pivot table is the answer (I don't think it is), but I've been directed twice by coworkers to consider "the pivot table". While they are great for summary information, totals and the likes, I don't think they're going to give me the answer I want. Are they?? Pivotal Decision Awaiting on Pivot Tables, JCH -- Dawg House Inc. "We live in it, therefore, we know it!" -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Pivot Table to Display Filtered Data Only
Thanks Dave. Appreciate the suggestion. I've been away for the last week.
I'll give this a try tomorrow and see where it gets me. Cheers, JCH -- Dawg House Inc. "We live in it, therefore, we know it!" "Dave Peterson" wrote: Maybe you can do something like this, but it looks very similar to Ron de Bruin's easyfilter. I added a sheet named Sheet2. I put my list of countries in B1:B25. I put a listbox from the Forms toolbar (not the control toolbox toolbar) on that sheet. I rightclicked on that listbox and selected Format Control. On the control tab, I put $b$1:$b$25 in the "input range" box I made sure that the "selection type" was Multi. Then I put a button from that Forms toolbar right next to the listbox. And assigned this macro to that button: Option Explicit Sub testme() Dim myLB As ListBox Dim iCtr As Long Dim oRow As Long With ActiveSheet Set myLB = .ListBoxes("List Box 1") .Range("a1").Resize(myLB.ListCount, 1).ClearContents oRow = 0 For iCtr = 1 To myLB.ListCount If myLB.Selected(iCtr) Then oRow = oRow + 1 .Cells(oRow, "A").Value = myLB.List(iCtr) End If Next iCtr End With End Sub And I used this in my helper column in Sheet1: =ISNUMBER(MATCH(A2,Sheet2!A:A,0)) And I could filter by that. =========== You may want to look at data|filter|advanced filter, too. Debra Dalgleish has some instructions: http://contextures.com/xladvfilter01.html Dawg House Inc wrote: Thanks again Dave. I don't think that's going to work for this situation as I basically want to have them select the "filter values" from a list. In a perfect situation, they'd select the values from a list box and the values would get filtered as the values were selected in the list box. I'm all about writing code...just not sure if its possible to pass in the values for the filter. ColA | ColB | ColC ------------------------------ Ctry1 | Val1 | Code1 Ctry2 | Val1 | Code2 Ctry3 | Val2 | Code2 ... Ctry99| Val15 | Code1 ================= Basically, I'd like the Col A values to be available in a single multi-select list that would impact the view of the filtered area of the spreadsheet. Make sense? -- Dawg House Inc. "We live in it, therefore, we know it!" "Dave Peterson" wrote: If I have a list of, say, states that I want to filter by, I could put that list in column A of another sheet. Then I could use a formula like: =isnumber(match(b2,sheet2!a:a,0)) If B2 contains the state name for that row. But you could use any formula you wanted: =or(a2="Smith",b2="mi",c21000) So I could retrieve the rows associated with Smith or Michigan or greater than 1000. It's really more of an ad hoc formula that does what it needs to do for that single purpose. Dawg House Inc wrote: Thanks for the suggestion Dave. I checked out the "easy filter" option, but that's not an option for me in this case. I am trying to take the spreadsheet and have a way to present the data in an easy to use (the users will be selecting the values to filter) way that doesn't require the user to do anything but select the data elements they want (or don't want) to see - multi-selecting would be great (if an option). I'm not a pivot table person per se, so I'm not sure on the capabilities of them nor their extensive features. I simply use them for summary information. As for your "formula in a helper column", would you care to share an example of this so I can see if it meets the needs of the users ? Thanks again. Cheers, JCH -- Dawg House Inc. "We live in it, therefore, we know it!" "Dave Peterson" wrote: If I want to filter on multiple values in a field (or even multiple values in multiple fields), I sometimes create a formula in a helper column and filter by that. I can make that formula as complex or as simple as I want. I could even use that helper column as a page field in the pivottable. Ron de Bruin has an alternative: http://www.rondebruin.nl/easyfilter.htm Dawg House Inc wrote: OK, I'm trying to do the following: I have a spreadsheet with the sample columns: Region | Country | Cust_Seg | Classification | twelve more columns Since Excel won't let me filter on more than two items using auto filter, I'm trying to utilize a pivot table (so I was told) to display the data I want. In short, I want the user to be able to select 0, 1, or many (even all) Regions and or Countries and or Cust_Segs and or Classifications (all the combinations) and display the other twelve fields as well with their related data. If I could use the auto-filter to allow them to pick/filter more than two items using the "custom" feature, then this would not be a problem. Also, I don't need to see any totals, counts, products, etc for the final results. I'm not even sure a pivot table is the answer (I don't think it is), but I've been directed twice by coworkers to consider "the pivot table". While they are great for summary information, totals and the likes, I don't think they're going to give me the answer I want. Are they?? Pivotal Decision Awaiting on Pivot Tables, JCH -- Dawg House Inc. "We live in it, therefore, we know it!" -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data table based on data table | Excel Discussion (Misc queries) | |||
Report choosing a Pivot table data | Excel Worksheet Functions | |||
Display columns side by Side in the Data Area of a Pivot table | Excel Discussion (Misc queries) | |||
pivot table | Excel Discussion (Misc queries) | |||
Display Text in the Data Area of a Pivot Table | Excel Discussion (Misc queries) |