Help with VB code to fine repeated values and produce a report

[rv02]

Hi All

I was hoping someone can can help with the following problem. Unfortunately I can not attach a sample file as for some reason the attachment button hasn't been working for me for a while.

I have included a sample below:
Customer No Expenditure Dept Date
123 120 Mens Mon
123 130 Sport Mon
456 140 Food Mon
789 150 Mens Tue
789 111 Children Tues
111 112 Food Wed
222 454 food Wed
222 574 Sport Wed
222 43 Children Wed
789 253 Children Thu

Table above shows the customers who have shopped in the store on certain day in one week. I need help with the code to do the following:
Look at customers and see whether a customer has bought from two different departments in one day (i.e. customer 123 but not customer 111). If they have bought an item from two different departments in one day then see if one of the departments was Mens. If it was men the give me the total expenditure for each department, i.e. for customer 123 list totals the expenditure for each of the department , Mens =120 , sport 130. But if they have shopped more than once in one day but haven't shopped in Mens then do not include them in the report. In the above table for example customer 789 will be reported for their shopping on Tuesday and not for Thursday as they only shopped once on thursday and on Tuesday one of the departments was Mens. But we will not report customer 222 because although they shopped in more than one department in one day, Mens wasn't one of the departments.

In my actual file I have over three thousand lines and I am trying to analyse the spending habits of customers who buy from Mens as where are they likely to shop next.

Thanks for your help in advance
I hope my example is clear

[Jacc]

No VBA, I couldn't muster up the energy. I did however make a formula solution using 2 dozen helpers or so, one being array formula.

A piece of VBA code can be written of course but I reckon my formulas can be greatly improved upon.
I also think some PivotTable wizard here on the forum can solve it in a much neater way.