How to display customers that have not bought an item in a pivot report

hows the datatable created ?
you really need an entry for product 1 - for all customers and a 0 entry where they have not purchased

how did you display 0 - as there are no entries for 0 - on the sample provided

The zero customers - is only showing customers who have ordered anything at all in Jan or Feb, NOT customers who did not order anything at all.

you have a list of customer in a sheet - 1 to 30
do you maintain a complete list of all your customers - do they have an ID _ my experinace is customer names can be entered differently - we can cover later

so the customers who ordered product 1 in Jan are
Customer 7
Customer 3
Customer 2
Customer 21
Customer 23
Customer 4
Customer 6
Customer 13
Customer 15

NOT Customer 30 as that was ordered in FEB but not in JAN

so customers who DID NOT order in JAN are
1,5,8,9,10,11,12,14,16,17,18,19,20,22,24,25,26,27,28,29 & 30

and so shouldn't your report show that list

OR am i misunderstanding what you are trying to do ?

EDIT - I see , a list on the customer data now

so i think that is what you are after - so can have a look - you will need to merge the lists somehow

it maybe better with a VBA solution
as you will need to create a list of customers for every product and date

However - importantly , are you only interest in the previous month - that will limit the data set
so you get a list of customers in Feb that have purchased - then you can create the FEB data , for all customers and products , using a lookup table
once created - copy and paste special - so no formulas are involved
then you can add the next month to the table

you say

This spreadsheet is to be used as a tool for the sales team that are not experienced excel users either so it needs to be simple and intuitive for them.

how will they USE
do you just want to send them a report or will they be doing manipulation of the data at all ?

how many customers do you have and how many products ?

Yes, what about the other questions at the bottom of my Post, you may not have seen as i edited the post

you say
This spreadsheet is to be used as a tool for the sales team that are not experienced excel users either so it needs to be simple and intuitive for them.

how will they USE
do you just want to send them a report or will they be doing manipulation of the data at all ?

how many customers do you have and how many products ?

Sorry Wayne you're right I didn't see the rest of the post

We will only be using this for a special group of customers approx 100 in total and we have approx 1000 products that they can purchase. The most common use would be to determine which customers haven't purchased specific newly launched products over a period of several months although from time to time it may be used for existing products as well.

The spreadsheet will be available on the server for the sales team to access and for them to filter any of the pivot reports to obtain the sales records they want to view, they have several pivots to choose from in the live spreadsheet. The data is updated at the beginning of each month to include the previous months data and there is currently 3 years of records in the data (50,000 rows). I was thinking that I could have another sheet that they would use to see which customers had not purchased a specific product and my aim was for them to just enter the product code in a field and for the formula to return the customer list for those customers with no sales for that product.

Thank you!
PS It's midnight here so I'm off to catch some shut eye and will check back in the morning

my aim was for them to just enter the product code in a field and for the formula to return the customer list for those customers with no sales for that product.

not sure if i can do with a formula - but can have ago
if its an extraction , I have done something like that for a warranty report -

so if they just add the product - do they also add a date range or is it just previous month
so now it would show FEB
then when MAR is loaded , just show MAR

Who does the loading of the data and updating the pivot tables (refresh ) etc ?

can you load a sample of real data and how structured - with dummy info in
customer ID
Products codes etc

then we have something to work on thats close to real , rather than your example and then need to change all the formulas for a real database structure

Good morning

not sure if i can do with a formula - but can have ago

Should I maybe close this post and re-post in the formula forum since it's not really a pivot problem? I don't want to waste your time on the wrong tasks!

so if they just add the product - do they also add a date range or is it just previous month
so now it would show FEB
then when MAR is loaded , just show MAR

Sometimes they would want a specific date/date range and other times they would want to just query the entire data range eg they may want a month/s, or quarter/s or year/s.
I have been thinking some more about this and it may be that I need to remove this from the original pivot reports spreadsheet and have this as a completely separate spreadsheet and I don't think that would be an issue.
Up until now, when this information has been required, I have exported the data from the WMS to an excel spreadsheet with a couple of control products (there are a couple of products that all customers should have bought at some time). Although this is not foolproof it has worked quite well as a method to obtain the info!
Once I have the data in a spreadsheet I remove the data for the control products and then just filter by the blanks for the product/s being queried which leaves a nice list of the customers that have not purchased the product for the period being queried. This of course works fine but I was hoping to automate the process in some manner as I can get multiple requests for multiple products throughout the month. Admittedly though it doesn't take a huge amount of time but it just means that they need to wait till I can get it done rather than being able to query it themselves from the central files. It probably goes without saying that they do not have access to the WMS system to extract the data themselves.

Who does the loading of the data and updating the pivot tables (refresh ) etc ?

I would upload and refresh the data at the beginning of each month with the previous month's data.

I have attached a copy of the data that is imported each month although this is only for 30 customers and 50 or so products but it does have all the columns etc that are in the original spreadsheet.

Thanks again for your help

Justmeok_Item sales Jan to Feb 2015.xlsx

i have made a pivot from your customers and then used a link for the product / dates from the pivot sheet

when you change any of the 3 inputs on zerosales , it should update the pivot table

use refresh and you will see the pivot table works OK - but the macro will run anyway and list all zero customers

EDIT _ Just tried in office 13 on a different PC and it works - just adding some error testing for the product ID - so if you put a product that does not exist - you will get zero for ALL customers - which maybe what you want
but in C1 see error handling

EDIT _ Your CUSTOMER G has a SPACE after the G - so does not match in your customer sheets and so returns zero rather than a value - see
product 3
you may have others like that

just tried in excel 2007 and it works OK

perhaps a screen shot of the macro sheet

heres mine