Return a subset of assets based on related criteria

[robertarm]

Hi guys,
I don't even know if this is a power pivot question or something able to be accomplished just in excel but I haven't been able to find a solution so far.
I have a list of work orders with all relevant info (standard job, comments, work order number, plant number etc.) What I want to be able to do is filter for all of the assets which have a workorder with a specific standard job, but then return all work orders attached to these assets not just the the ones with this job code.
All of the info is in a flat table at the moment in a single excel sheet (extracted from other databases into .xls format).
The standard job I want to filter on will be different each time (There is a finite number of options, maybe 40), but I would like the sheet to be able to be easily manipulated by people who are not even close to being excel power users.
Ideally I would like it to return a pivot table listing all of the assets, the various standard jobs so these can be summed, but most importantly be able to be expanded from the pivot table to return the full list of filtered results so they can be used to issue work and ensure when someone goes to an asset every job is issued efficiently.

Excel 2010

	A	B	C	D	E	F	G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Work Group[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Work Order No[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Plant No[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Work Order Desc[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Standard Job No[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Standard Job Desc[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Comments[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]04316305[/TD]
[TD="bgcolor: #FFFFFF"]DS00501[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF"]1DOFSW[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF"]insulation on drop out fuses perished[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]04354555[/TD]
[TD="bgcolor: #FFFFFF"]DS00547[/TD]
[TD="bgcolor: #FFFFFF"]Oil Stain[/TD]
[TD="bgcolor: #FFFFFF"]1OILST[/TD]
[TD="bgcolor: #FFFFFF"]Oil Stain[/TD]
[TD="bgcolor: #FFFFFF"]OIL STAIN ON BOTTEM EDGE OF SUB[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]04442514[/TD]
[TD="bgcolor: #FFFFFF"]DS00566[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF"]1DOFSW[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF"]roadside expulsion fuse deteriorated[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]04401307[/TD]
[TD="bgcolor: #FFFFFF"]DS00593[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]1OILLE[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]oil leak from drain[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03654611[/TD]
[TD="bgcolor: #FFFFFF"]DS00629[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]1OILLE[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]chek oil leack at tap and at top of sub[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]04542591[/TD]
[TD="bgcolor: #FFFFFF"]DS00658[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF"]1DOFSW[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF"]dof fuses perrished[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]04691703[/TD]
[TD="bgcolor: #FFFFFF"]DS00727[/TD]
[TD="bgcolor: #FFFFFF"]3x expulsion fuses deteriorated[/TD]
[TD="bgcolor: #FFFFFF"]1DOFSW[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03642707[/TD]
[TD="bgcolor: #FFFFFF"]DS00756[/TD]
[TD="bgcolor: #FFFFFF"]Vermin[/TD]
[TD="bgcolor: #FFFFFF"]1VERMN[/TD]
[TD="bgcolor: #FFFFFF"]Vermin[/TD]
[TD="bgcolor: #FFFFFF"]birds nesting[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03958717[/TD]
[TD="bgcolor: #FFFFFF"]DS00760[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]1OILLE[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]repair oil leak[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]04448138[/TD]
[TD="bgcolor: #FFFFFF"]DS00861[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF"]1DOFSW[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF"]expulsion fuses x 2 deteriorated[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03855113[/TD]
[TD="bgcolor: #FFFFFF"]DS01353[/TD]
[TD="bgcolor: #FFFFFF"]Oil Stain[/TD]
[TD="bgcolor: #FFFFFF"]1OILST[/TD]
[TD="bgcolor: #FFFFFF"]Oil Stain[/TD]
[TD="bgcolor: #FFFFFF"]oil stain at drain plug[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03855114[/TD]
[TD="bgcolor: #FFFFFF"]DS01353[/TD]
[TD="bgcolor: #FFFFFF"]Replace Surge Diverter[/TD]
[TD="bgcolor: #FFFFFF"]1SURGE[/TD]
[TD="bgcolor: #FFFFFF"]Replace Surge Diverter[/TD]
[TD="bgcolor: #FFFFFF"]old red insulators require replaceing[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]04318027[/TD]
[TD="bgcolor: #FFFFFF"]DS01543[/TD]
[TD="bgcolor: #FFFFFF"]Repair/Replace HV Hardware[/TD]
[TD="bgcolor: #FFFFFF"]1HVHAR[/TD]
[TD="bgcolor: #FFFFFF"]Repair/Replace HV Hardware[/TD]
[TD="bgcolor: #FFFFFF"]TYPE: Distribution Substation UG , ASSET NO: 1543 , DESCRIPTION: UG Dist Sub 1543, Padmount HV Switchgear , LOCATION: Roony Ave, Abbotsbury 2176 Switchgear moves excessively when operated-requires resecuring to plinth Passed on to Brendan Leshone[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]04518812[/TD]
[TD="bgcolor: #FFFFFF"]DS01714[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF"]1DOFSW[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF"]replace degraded drop out fuses[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]04528990[/TD]
[TD="bgcolor: #FFFFFF"]DS01714[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF"]1DOFSW[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF"]replace degraded drop out fuses[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03688963[/TD]
[TD="bgcolor: #FFFFFF"]DS01873[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]1OILLE[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]oil leak Contestable Works advised sub will be replaced under URS project in June 2015[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03698639[/TD]
[TD="bgcolor: #FFFFFF"]DS01879[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]1OILLE[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]oil leak on sub[/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03932426[/TD]
[TD="bgcolor: #FFFFFF"]DS01879[/TD]
[TD="bgcolor: #FFFFFF"]REPLACE HV CROSSARM[/TD]
[TD="bgcolor: #FFFFFF"]1HVARM[/TD]
[TD="bgcolor: #FFFFFF"]Replace HV Crossarm[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]04518801[/TD]
[TD="bgcolor: #FFFFFF"]DS01946[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF"]1DOFSW[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF"]replace degrade drop out fuses[/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03620714[/TD]
[TD="bgcolor: #FFFFFF"]DS02275[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]1OILLE[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]OIL LEAK FROM SUB 2275[/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03934712[/TD]
[TD="bgcolor: #FFFFFF"]DS02275[/TD]
[TD="bgcolor: #FFFFFF"]REPLACE HV CROSSARM[/TD]
[TD="bgcolor: #FFFFFF"]1HVARM[/TD]
[TD="bgcolor: #FFFFFF"]Replace HV Crossarm[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03547607[/TD]
[TD="bgcolor: #FFFFFF"]DS02403[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]1OILLE[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]oil stain on sub[/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03931960[/TD]
[TD="bgcolor: #FFFFFF"]DS02403[/TD]
[TD="bgcolor: #FFFFFF"]REPLACE TRANSFORMER[/TD]
[TD="bgcolor: #FFFFFF"]1TXREP[/TD]
[TD="bgcolor: #FFFFFF"]Replace Transformer[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03932388[/TD]
[TD="bgcolor: #FFFFFF"]DS02403[/TD]
[TD="bgcolor: #FFFFFF"]REPLACE HV CROSSARM[/TD]
[TD="bgcolor: #FFFFFF"]1HVARM[/TD]
[TD="bgcolor: #FFFFFF"]Replace HV Crossarm[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]04012529[/TD]
[TD="bgcolor: #FFFFFF"]DS02458[/TD]
[TD="bgcolor: #FFFFFF"]Repair/Replace HV Hardware[/TD]
[TD="bgcolor: #FFFFFF"]1HVHAR[/TD]
[TD="bgcolor: #FFFFFF"]Repair/Replace HV Hardware[/TD]
[TD="bgcolor: #FFFFFF"]replace drop out fuses[/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03769406[/TD]
[TD="bgcolor: #FFFFFF"]DS02750[/TD]
[TD="bgcolor: #FFFFFF"]Replace Surge Diverter[/TD]
[TD="bgcolor: #FFFFFF"]1SURGE[/TD]
[TD="bgcolor: #FFFFFF"]Replace Surge Diverter[/TD]
[TD="bgcolor: #FFFFFF"]old style brown serg diverter[/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03183383[/TD]
[TD="bgcolor: #FFFFFF"]DS02929[/TD]
[TD="bgcolor: #FFFFFF"]Repair/Replace ABS Arcing Horn[/TD]
[TD="bgcolor: #FFFFFF"]1ABSAH[/TD]
[TD="bgcolor: #FFFFFF"]Repair/Replace ABS Arcing Horn[/TD]
[TD="bgcolor: #FFFFFF"]arching horn needs removing[/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]04316310[/TD]
[TD="bgcolor: #FFFFFF"]DS03042[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF"]1DOFSW[/TD]
[TD="bgcolor: #FFFFFF"]Replace DOF Assembly[/TD]
[TD="bgcolor: #FFFFFF"]drop out fuses insulation frayed[/TD]

[TD="align: center"]30[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03596589[/TD]
[TD="bgcolor: #FFFFFF"]DS03325[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]1OILLE[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]check oil leak[/TD]

[TD="align: center"]31[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03931937[/TD]
[TD="bgcolor: #FFFFFF"]DS03325[/TD]
[TD="bgcolor: #FFFFFF"]REPLACE TRANSFORMER[/TD]
[TD="bgcolor: #FFFFFF"]1TXREP[/TD]
[TD="bgcolor: #FFFFFF"]Replace Transformer[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03659010[/TD]
[TD="bgcolor: #FFFFFF"]DS03338[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]1OILLE[/TD]
[TD="bgcolor: #FFFFFF"]Oil Leak[/TD]
[TD="bgcolor: #FFFFFF"]light oil leak[/TD]

[TD="align: center"]33[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03932406[/TD]
[TD="bgcolor: #FFFFFF"]DS03338[/TD]
[TD="bgcolor: #FFFFFF"]REPLACE BROKEN MDI UNIT[/TD]
[TD="bgcolor: #FFFFFF"]1MDIUN[/TD]
[TD="bgcolor: #FFFFFF"]Replace Broken MDI Unit[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03659026[/TD]
[TD="bgcolor: #FFFFFF"]DS03341[/TD]
[TD="bgcolor: #FFFFFF"]Repair/Replace LV Hardware[/TD]
[TD="bgcolor: #FFFFFF"]1LVHAR[/TD]
[TD="bgcolor: #FFFFFF"]Repair/Replace LV Hardware[/TD]
[TD="bgcolor: #FFFFFF"]meter box wire hanging out of sub[/TD]

[TD="align: center"]35[/TD]
[TD="bgcolor: #FFFFFF"]CHOXPM1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]03748162[/TD]
[TD="bgcolor: #FFFFFF"]DS03581[/TD]
[TD="bgcolor: #FFFFFF"]Replace HV Insulator[/TD]
[TD="bgcolor: #FFFFFF"]1HVINS[/TD]
[TD="bgcolor: #FFFFFF"]Replace HV Insulator[/TD]
[TD="bgcolor: #FFFFFF"]replace chipped insulator[/TD]

</tbody>

Sheet1​

I'm running Excel 2010 on Windows 7.

 

[LloydFinancials]

Will you provide an example given your data here?

 

[robertarm]

Sure,
If I filter a pivot table for standard job 1HVARM. I get 3 results

Excel 2010

	A	B	C
Standard Job No	1HVARM
Plant No	Work Order No	Work Order Desc
DS01879	REPLACE HV CROSSARM
DS02275	REPLACE HV CROSSARM
DS02403	REPLACE HV CROSSARM
Grand Total

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]03932426[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]03934712[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]03932388[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet8​

However I want to see all wokorders for the 3 assets which have a workorder with this standard job (I have applied the filter to the assets below for this example, but in my full sheet there are 40,000 WO's and 30,000 assets so it isn't a realistic option this way).

Excel 2010

	A	B	C
Plant No	Work Order No	Work Order Desc
DS01879	Oil Leak
REPLACE HV CROSSARM
DS02275	Oil Leak
REPLACE HV CROSSARM
DS02403	Oil Leak
REPLACE TRANSFORMER
REPLACE HV CROSSARM
Grand Total

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]03698639[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]03932426[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]03620714[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]03934712[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]03547607[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]03931960[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]03932388[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet8​

 

[Ozeroth]

Interesting problem!

The core of the problem is that you want to select a Standard Job No, and filter your PivotTable not just to those Standard Job Nos but to all Plant Nos that have a work order with that Standard Job No. This means a filter directly on the Standard Job No column won’t work.

I’ve uploaded a file with a sample solution (Excel 2010 PowerPivot):
https://drive.google.com/file/d/0B9pNjpDQKy_LM09KMjdaM2N4N1k/view?usp=sharing

My method is to create a second table containing Standard Job Nos, relate your main table to it using an inactive relationship, use a slicer on this lookup table to select a the Standard Job No, then use DAX to create a measure that is used to filter Plant No.

The end result is that the user just selects a Standard Job No on a slicer and the PivotTable filters as you’ve described.

These are the steps I followed:

1. Create a “Standard Job Number Selection” lookup table that contains distinct Standard Job Nos (and descriptions if you want). I have done this manually but you should use Power Query or similar.
2. Add a relationship from your main table’s “Standard Job No” column to the Standard Job No Selection column in the new table.
3. Mark this relationship as inactive, so that it doesn’t automatically filter your PivotTable.
4. Create a PivotTable with the row labels you require, including at least Plant No. I’ve included Plant No, Work Order No & Work Order Desc as per your example.
5. Add a slicer for the “Standard Job No Selection” column from the “Standard Job Number Selection” table.
   At this stage, the PivotTable will still be unfiltered, showing all work orders.
6. Create a measure which will act as a flag indicating whether to include the current plant. This measure will return 1 only if there is a single plant present in the filter context and that plant has at least one work order with the selected Standard Job No. The red CALCULATETABLE section creates the list of “valid” plants by using the inactive relationship to filter the main table. The VALUES ( Data[Plant No] ) in green gives us the list of plants in the current context (which is just a single plant at a time in the PivotTable I have set up). The IF(HASONEVALUE(...)) will be evaluated in the context of the intersection of the red and the green, which will be the current plant only if it is “valid”, in which case the measure will return 1, otherwise blank.
   

   [COLOR=#000000][B]Flag - Single Plant has Selected Standard Job[/B] :=
   CALCULATE (
       IF ( HASONEVALUE ( Data[Plant No] ), 1 ),[/COLOR]
       [COLOR=#008000]VALUES ( Data[Plant No] ),[/COLOR]
       [COLOR=#ff0000]CALCULATETABLE (
           VALUES ( Data[Plant No] ),
           ALL ( Data ),
           USERELATIONSHIP ( Data[Standard Job No], StandardJobNumberSelection[Standard Job No Selection] )
       )[/COLOR]
   )

   <strike></strike>
7. In the PivotTable, filter the Plant No column (drop-down or right-click Filter) by selecting Value Filter: “Flag – Single Plant has Selected Standard Job” equals 1.
8. Now if you select one (or more) Standard Job Nos on the slicer, the PivotTable will be filtered appropriately.
9. Note: In case you later want to add a separate filter on Plant No (e.g. a slicer to further limit the plants you are looking at), go into PivotTable options and enable “Multiple Filters per Field”.

 

[ImkeF]

Interesting problem!

Hi Ozeroth,
these were my thoughts as well. Tried to find a Power Query solution for it and failed.

Now reading your Power Pivot solution on it was a real eye opener: If I could only get my head around these context things in DAX (they still are a moving target for me): What an elegant solution to come up with!
This really motivated me to get back into DAX.

But on the other hand, the description you’ve provided ordered some thoughts and made the Power Query solution appear:

let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
Merge = Table.NestedJoin(Source,{"Plant No"},Source,{"Plant No"},"NewColumn",JoinKind.Inner),
Expand = Table.ExpandTableColumn(Merge, "NewColumn", {"Standard Job No", "Standard Job Desc"}, {"Standard Job No.1", "Standard Job Desc.1"})
in
Expand

So you simply load the table and join it with itself on Plant No: The column whose matches on shall be returned. Then expand on the Job No’s and descriptions to show: Done.

... thought that this will make you smile

Thank you!

LinkToFile

 

[Ozeroth]

Hi Imke,
Glad to have motivated you back to DAX!
I like the simplicity of your "join the table with itself" method - not something I would have thought of

robertarm - I would be interested in how large a table you end up with following Imke's method on your full dataset. Is either method preferable in your situation?

All the best!

 

[robertarm]

Ozeroth and Imke,
thanks for your feedback it is greatly appreciated.
I was starting to think I would have to get creative with VBA to push out a solution for me.

I have so far implemented Ozeroth's solution on my full sized table I currently have which is only 8000 records and it all works fine. My system does lag for 5 seconds or so with some operations when you play with the table, but overall not something I am hugely concerned with. I will wait and see how it goes on the full list of 40,000 if I ever need to run it on that table.

I don't fully understand the solution but can decipher most of it (I only got powerpivot recently so I could get distinct count in a few tables I needed and have been having a bit of a play since then) from other reading I was doing trying to find a solution to this myself. I found nothing which brought me close to this, but it is a hard problem to find the right search term for,

ImkeF, thanks for your input too. Unfortunately I can't access your file because it is asking me for a username and password to a live account.
I actually put powerquery on also hoping there would be an elegant out of the box solution for what I wanted but there was nothing and it appeared to be very foreign to me and wasn't intuitive to me at all.
What does that query actually do and how do you do the final sorting etc?

At the end of the day I need this to produce a pivot table/table for people far less excel savvy than myself (and primates compared to what you 2 have proven) who really are only basic excel users so simplifying the final product for them to chop and change their filters for different standard job numbers is the main objective for me.
Ultimately the data for this will be a daily update using cognos from our corporate database. I am currently getting this dumped in xls for ease of use but will have to find a way for this filterable table to look up that info.

Again, thank you very much for your input in getting me to this point where I have something which works.

If you have some pointers on which solution would be optimal going forward on the additional info please let me know, but what I have already does what I need.

 

[robertarm]

I have just found one limitation.
I can't drill through the table to export all of the details on every WO to a new sheet as you would in a normal pivot table.

Doing that just exports everything unfiltered.
Is there a way to handle this?
Is Imke's solution better to handle this? but I still have the questions above around usability for the general public.

 

[ImkeF]

Sorry about the link, wasn't aware of the limitation.
Try this one please: http://www.thebiccountant.com/download/456/

For the drilldown you need a measure in your pivottable. I've included it in my solution. (Also the Grand Total for all of them)

I'm expecting the filtering on the JobNo to be very fast, but have not tested it - so I would very much welcome your feedback on this

Loading the updated Input data into the model should take longer than Ozeroths, because the Power Query calculation has to be done as well, but once this is done - at least in theory - it should be very fast.