Forum Discussion
Complicated formula to search for certain data
Your problem can be solved with a pivot table. Put the [Value Date] in the Values and [Customer] in the Rows. Then change the valuesettings to Max instead of Sum. Then your pivot will show the last order date for each Customer.Then sort your Pivot on the Max column and your Customers will be ordered on their last order date.
Best regards,
Martijn
No it can't. Maybe I explained my problem poorly. It's not about their last order but how they shopping pattern changed. For instance they bought my product in first, second and third month, but then they stopped or cut down orders because of some reason. And I need to find out which clients are doing that. I think that this is to complicated filer to apply in pivot table, belive me I tried pivots with poor outcome :) If you have an idea how to resolve that in pivots I'm listening.
Thank you for your answer, regards!!
Hi Bartosz,
If you need to select customers who didn't paid back few months from the given date you may add helper column which calculates such customers like
=IF(SUMPRODUCT(($D$2:$D$82=$D2)*($A$2:$A$82>=($U$1-90))),"Pays", "Didn't pay")
after that create pivot table on your range with customers name and filter it on ones who didn't pay, like:
You may complicate above filtering logic as needed, but in any case it returns list of customers for fixed date.
If you need to see at once for the range of dates as variant -create separate table with dates only, link it to your table and create a pivot with columns from dates table, rows with costomers and values mentioning is payment delayed or not on column date. How to implement depends, in particular, on your Excel version.
Sample is attached.
Hi,
First I would like to thank you for your answer.
How does your formula exatcly work?
Cause when I aplied it in real file it almost always shows "didnt pay", and it was odd for me so I checked manually, and even with clients that made dozens of transactions every month it shows "didnt pay".
I uploaded w screen of how this formula work for one of clients who made transacation in evey month, and even within the same month the formula works diffrently. If you could tell what I might need to do to correct this formula.
I'll explain this once again, I need to filter this data, like formula should at first divide whole database into clients, than sum the volume or number of their transactions in every month, and if the number in next month compared to the previous in which client made transaction is 0 or decresead badly(lets say by 40%) than show these clients in the outcome-like in your table. I hoped that your formula after some personalization could made this. Once again, thanks for your reply!
I'll give more details later on today, right now just 40 minutes before my flight. Could you please copy your formula and paste it here to check how exactly you use it.
And please clarify when you compare monthly transaction you take average for some previous months or compare current month to only previous.
