Pivot Table That Looks At Lookup Table Before Posting Results

[meppwc]

Is there a way to setup a pivot table to exclude records found based on a lookup table before results are posted?


I have a 3 worksheet spreadsheet:
1 - Data Dump - all records from a trouble ticket system that includes a column called "User ID"
2 - Lookup - includes a table with User IDs in column A
3 - Summary - contains summarized information including a pivot table

What I am wanting, is before the pivot shows results of the User ID field, it firsts looks at the User ID column in the lookup table and excludes any record found that is present in the lookup table.

The end result is that the Pivot Table will only show records that are NOT found in the lookup table.

Using a pivot table is the only way I can think of displaying those records. If there is another more practical way of accomplishing this I am interested in knowing.

 

[Jacob Barnett]

Hi,

Its debatable whether this is actually a PowerPivot question - if it is post back and I will tell you how get started (include the version of excel you are using).

This is also easily solved without PowerPivot, just had a column to your data dump that contains a formula that looks something like:

=IF(COUNTIF(Lookup!A:A,A2)>0, "Exclude", "")

This assumes your Lookup IDs are in column A of a worksheet called Lookup and the ID in question is in cell A2 of the current sheet.

Expand your Pivot data range to include the extra column and you can use as a Report Filter.

 

[Jerry Sullivan]

Hi all,

Jacob's suggestion to add a field to the source data which can be used as a Report Filter is a relatively easy way to accomplish this, provided that it's acceptable to have the excluded data "in" the PivotTable but filtered.

If you are excluding this data because the pivot table users shouldn't have access to it then you'll need an approach which excludes the data from the Pivot Cache. For that you could use a query to filter the data.
PowerPivot, as Jacob noted, is one tool that can do this and it can be installed for free if you have xl2010 or later. Other methods include using MSQuery or ADO which can be used with earlier versions of Excel.

Jacob, Where's your new Avatar?

 

[Jacob Barnett]

Jerry - you tell me, I did it last night and there's no sign of it today . I'll have another go when I get home.

 

[meppwc]

Jacob and JS411
Thank you so much for the suggestion...........the formula =IF(COUNTIF(Lookup!A:A,A2)>0, "Exclude", ""), once I changed A2 to N2 worked.
I inserted it in column AR of the Data Dump worksheet and the end result is..........the cell with the formula contains the word "Exclude" if the User ID for that row also exists in the lookup table.
If the User ID for that row IS NOT in the lookup table, the cell is blank or null.

So it seems I am one step closer to my objective.

Now all I want to do, on the Summary worksheet, is display those records found that are blank or null
And the display would be taken from rows A and N from the Data Dump worksheet
In this case, it would be a total of 4 records (using the data I currently have)
Note, that of those 4 records, 3 are assigned to IDs not in the lookup table and the 4th one is blank or null (not assigned to anyone)

How would I go about displaying those records (using Data Dump columns A and N) on my Summary worksheet?

 

[meppwc]

update

I was able to modify the formula you provided and then run a VBS in the background..........works great.........thanks again for your help