Using Pivot Table to Display Filtered Data Only

[Guest]

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

 

[Dave Peterson]

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

 

[Guest]

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!"

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

 

[Dave Peterson]

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",c2>1000)
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.

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

 

[Guest]

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!"

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",c2>1000)
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.

 

[Dave Peterson]

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

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?

 

[Guest]

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