Apply Filter

[OCM]

Greetings,

I have a large data set whereby I need to calculate the number of investigations (unduplicated) worked on by auditors for a given fiscal year. The case MUST have a primary auditor, but multiple auditors may work on the same case as secondary auditors etc.

To count the cases only once, I use auditor type = Primary, but some cases are missing this information and I’m trying to find a way to identify cases without a primary auditor. (expected result included)

Any ideas?



TIA,

 

[dhookom]

A simple pivot table results in this:

 

[OCM]

Thanks Duane,
Your pivot solution results the expected result. Once in pivot, I attempted to apply filter but didn't get the expected result.
C002501 shouldn't be in the list as the case has a primary auditor
C002511 is missing in the list. Since it doesn't have a primary auditor, it should be listed.



TIA

Regards,

 

[combo]

Excel has two handy Power BI tools: Power Query and Power Pivot for data manipulation. You can find both solutions in attached workbook. In both cases source data have to be converted to table first. Next:
1. Power Query:
A series of simple (recorded) queries, create full list of cases, a list of cases with primary auditor, a list of cases with secondary auditor. Next, combine the first list with the two other, output to workbook and filter.
2. Power Pivot:
Add source table to data model, add two measures to count primary and secondary auditors. Next create pivot table from data model. Adjustments:
- columns show all data,
- display 0 for empty data,
- filter data where 'N_primary' = 0.

 

[OCM]

combo,
Thanks for your feedback.
Your suggestion is a good one. Moving forward, I'll explore your method.

I was able to obtain the expected result as follows:
1. I created a helper column on the pivot table and use the following formula where column B is the "primary" column:

=IF(ISBLANK(B5), "Empty", "Not Empty")

2. I then applied filter to see only "Empty"

The above gave me what I was looking for. What I would like next is to display the filtered records along with all records (A - AH) onto a separate sheet.

A	B	C	D	…	…	AH
Case ID	Auditor	Auditor Type	Date Audited			Amount Identified

TIA

Regards,

 

[mintjulep]

Embrace Boolean.

=ISBLANK(B5)

Filter on TRUE

To show things on a separate sheet, use the FILTER() function.

If you have a newish version of Excel you could also use the PIVOTBY() function.

 

[OCM]

JP, thanks!
Yes, your Boolean method gave me the same result. I'll use this method moving forward. I use office 360, but I'm not familiar with PIVOTBY () function to show the filtered records on a on a separate sheet.

TIA

Regards,