pivot table to multiple consolidate ranges

I have a worksheet I'm trying to consolidate into a pivot table. Here's an example of how it looks:

Clipboard01.jpg

I'm trying to use multiple consolidate ranges so that the lot information combines but I can filter the pivot table based on customer and/or type. Is there a way to do this? I'd like the pivot table to look like this:

Clipboard01.jpg

I'd like to be able to select a customer and/or type from the drop down and have the pivot table filter the lots and quantities accordingly.

So far I'm only able to consolidate based on lots and quantity. If I add in the customer or type then it doesn't work.

Thanks in advance!

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Here's a sample file

is that what you want?

done with PowerQuery and PivotTable

Power Query for

• Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
• Excel 2016 or Excel 365 is built-in, aka Get&Transform
• PowerQuery works with M-language and does not contain any classic formulae or vba code
• What PowerQuery is? see here: Getting Started with PowerQuery (Get&Transform) in Excel

you must have PowerQuery installed first

Well unfortunately PowerQuery won't work for our specific application. Is there any way of doing this using Multiple Consolidate Ranges with a standard pivot table like I was initially attempting?

Thanks again

Alt, D, P - but you did it
you can try reorganize table via formula/vba then use PivotTable
or try with PowerPivot (add-in for 2010)

I'll play around with it some more.

Thanks again for your help!

You are welcome

Have a nice day

here is PQ and PT with correct results

just for fun

The table doesn't affect my vba but I still can't get power query to work without converting the original range into a table. Is that possible?

of course you can't use range with PowerQuery on the same workbook bacause PowerQuery works with Tables
you can try use PowerQuery from external workbook if this make sense to you