Filtering on a Pivot table

Question

Hello - I have some raw spend data for my team. We have supplier spend against a contract id and supplier spend without a contract id. In the contract id field, the value is either the contract id or the word "Unknown". I have created a pivot table: rows are supplier name, then contract id. See image. I am trying to filter this pivot table to only show suppliers where there is spend with and without a contract id such as with Supplier A. Can anyone share how I might accomplish this?

Answer 1

Hi,

You may download my solution workbook from here. Add data by rows to the blue range and click on Data > Refresh All.

Hope this helps.

Answer 2

You are welcome.

Answer 3

Thanks so much Ashish! This worked!

Answer 4

Hi Ashish,

Yes, in this case, I would only want to show Supplier A, C, and D along with their contract ids. This is just a small sample but in my actual file I have over 750K rows with roughly 2K suppliers. I do have access to PowerPivot. I don't have a website that I can share, can you just copy this data below into a spreadsheet? I will try to get access to share with external parties.

Supplier Name	Contract ID	Amount
Supplier A	A123XX	$500.00
Supplier A	A124XX	$350.00
Supplier A	A125XX	$100.00
Supplier A	Unknown	$50.00
Supplier B	Unknown	$2,000.00
Supplier C	A126XX	$3,000.00
Supplier C	Unknown	$2,000.00
Supplier D	A127XX	$3,500.00
Supplier D	Unknown	$500.00
Supplier E	A1278XX	$2,500.00
Company L	B5208XX	$500.00
Supplier A	A123XX	$35.00
Supplier A	A124XX	$51.00
Supplier A	A125XX	$903.00
Supplier C	A126XX	$111.00
Supplier C	Unknown	$294.00
Supplier E	A1278XX	$2,500.00

Thanks!

Answer 5

Hi,

So you only want to see suppliers A, C and D along with their respective contractors because these are the suppliers who have specific ID's and unknown? Is my understanding correct? I think i can solve this with the PowerPivot. Does your version of MS Excel support the PowerPivot? If yes, then share the download link of your MS Excel file.