rosaW

New Member
Joined
Aug 14, 2009
Messages
13
Hello,

I have created a query to show on which week numbers of the year, customers have bought products. I now want to show the week numbers in which each customer has not bought anything.
Can anyone explain how to do this?

Greatly Appreciated!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

tjlindow

New Member
Joined
Aug 10, 2009
Messages
10
Hi Rosa,

Can you post a screen shot with some made-up data? It's difficult to propose a solution without having an idea of how the source data looks.

TJL
 

tjlindow

New Member
Joined
Aug 10, 2009
Messages
10
Rosa,

The easiest solution to see the weeks where a customer did not buy would be to create a pivot table with strCustomerID in the rows, dtm Year and dtmWeek in the columns, and count on dtmWeek. Blank values in the resulting pivot table would indicate weeks where the customer did not buy; a value of 1 would show the customer did make a purchase.

TJL
 

rosaW

New Member
Joined
Aug 14, 2009
Messages
13

ADVERTISEMENT

Thank you that worked! Now i only need to show the customers with the weeks where there are blank values or did not purchase. How would i set up a filter for this?
 

tjlindow

New Member
Joined
Aug 10, 2009
Messages
10
First, you would need to remove the Grand Totals for columns and rows. Then you could add a formula in a cell that is well to the right of the pivot table (so that the formula isn't immediately overwritten by the Pivot Table when additional data is added). If you choose to put the filter formula in Column AA, the formula would be:

=SUM(B$4:Z4)-COUNTA($B$3:Z$3)

This formula assumes your column labels (dates) are in row 3 and your first column of pivot data is in B4. "0" values for the formula would imply that the customer has made purchases in each week - you could filter these out. Values that are >0 mean that the customer has missed one or more weeks.

TJL
 

rosaW

New Member
Joined
Aug 14, 2009
Messages
13

ADVERTISEMENT

I don't know if this makes a difference or if you realized this, that i am using MS Access.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,979
Messages
5,767,432
Members
425,412
Latest member
andrealp4444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top