Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-09-2017, 08:46 AM
Squall LH Squall LH is offline PIVOT TABLES adding several FILTERS that do not intersect between them Windows 8 PIVOT TABLES adding several FILTERS that do not intersect between them Office 2016
Novice
PIVOT TABLES adding several FILTERS that do not intersect between them
 
Join Date: Nov 2017
Posts: 1
Squall LH is on a distinguished road
Question PIVOT TABLES adding several FILTERS that do not intersect between them

Greetings,



I have this problem that I am unable to solve. IŽll try to explain as best as I can, and IŽd like to thank in advance whoever makes the effort to read it:
I have this data of "IDs" by rows whit many columns fields, "Type, Location, Task 1, Task 2, Task 3 until 18". Inside the fields of "Task" there are ID entries that say "None", and those are the ones that I am interested in. Something like this:

ID Type Location Task 1 Task 2 Task 18
Yes None Yes
None yes None


I need a pivot table, with a slicer by the field Location", that gives the total count of IDS and then the count of entries in Task 1 that have the value "none", the count of Task 2 that have the value "none" and so on. Should look like this:

Rows by type Total IDS Task 1 (filter: only "None") Task 2(filter: only "None")
a 500 50 100
b 23 etc
c 40



So I create a Pivot table, and I add as values the "count" of the IDs, putting on the rows the Type and as values Task1, Task2 etc. I don't add them as columns because it immediately starts to add them as subgroups. Then I add as filter "Task 1" for example. What I get is that if I filter so it only shows the Task 1 with the entry "None", all the rest of columns are filtered with the same criteria. If I instead, add several filters(let's say Task 1 and Task 2), I get the count of only the data that meets the condition of both of the filters.

How can I achieve a pivot table with filters for every field in each column? What I have tried so far:

-Creating 18 pivot tables each one with their own filter, connecting their slicers and arranging them so it would look like a single pivot table. The problem here is that not all the fields "Tasks" have entries for every single name on the field "Type". So when I touch the slicer by location, everything loses the arrangement and the order is then blasted. If there would be a way to lock the rows this could work.

-Trying to add the fields "Task" as columns, but again and again, it creates subgroups, one inside of the other. It does not let me just add them at the same level

Note: I haven't ever used VBA, but if that is what it takes IŽll get to it. Still, if there is a creative solution that allows me not to get into VBA, I'll take it.

Thank you for the help.
Reply With Quote
Reply

Tags
filter, pivot tables

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
PIVOT TABLES adding several FILTERS that do not intersect between them Pivot Tables lilita123 Excel 1 04-01-2017 02:24 PM
Pivot Tables MSA5455 Excel 2 02-03-2015 06:02 AM
Intersect tinfanide Excel Programming 1 07-27-2014 06:14 AM
Mail Merge - Filters adding condition not originally specified bobcharest Mail Merge 11 06-13-2014 11:03 PM
Independent tables/filters Wries Excel 0 07-28-2009 04:16 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 03:40 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft