Help needed badly! Subfilter on values?

**milleniummanp7** · 06-20-2013, 07:20 AM

Kind of tricky to explain, but I badly need help with this...it drives me crazy every day. I've attached a file containing dummy numbers to help.

I have a report with a lot of data points, but the ones included are primary. As you can see, there are five order numbers split over 12 lines, so there are multiple lines for one order number.

I need to display all the lines under each order number as one. ie - Merge the values together for that order number. Easy enough manually with the small example I've provided - not with thousands of lines.

The whole point of this exercise is to display all order numbers which have multiple amounts of P1, and 0 P2.

I figured the easiest way to do this would be a pivot. In this case we can see it will applies only to the BA order (2xP1 and 0xP2). However, I need to be able to hide all orders that do NOT meet this criteria. So BA should be the only order left showing in the list.

Basically, I need a way to filter the values in a pivot table in the same way that you can do with an autofilter (number filters P1>1, P2=0)

I'm sure there's a simple fix for this and I'll be eternally grateful to whoever can help me, because this request is driving me round the twist! I'm on Office 2010

Thanks guys!

dummydataimage.PNG

dummydata.xlsx

EDIT:// I tried a slicer, but it is filtering each individual line rather than the TOTAL for that order number. Is it possible to slice by total? I think this might just be what I need to do....

**powdow** · 06-20-2013, 07:36 AM

Have you tried adding slicers? Attached I added two slicers, one for P1 and one for P2. Then I selected everything greater than 1 for P1 and 0 for P2.

**milleniummanp7** · 06-20-2013, 07:42 AM

Thanks for the reply.

I just made an edit to the initial post...I have tried slicers, but I think the problem is that it is still working with the individual lines rather than the total.

If you look at the unedited pivot table, the only order number with >1 P1 and =0 P2 is 0004 for BA. So this should be the only one left displayed. The slicer doesn't do that....I think what it is doing is removing from the single lines....not sure if that makes sense, I didn't explain it very well.

Thanks,

**powdow** · 06-20-2013, 08:22 AM

Well, this is a little clunky, but if you copy your pivot table and paste values to a new area. Then add a column with a formula that will show just the sum values of P2. Then filter on the new column. See attached.

**milleniummanp7** · 06-20-2013, 11:35 AM

Thanks, I think this works, but not sure of how it would work on a report with tens of thousands of lines....

Something tells me there is a much easier automated solution out there....if I could just find it