Complicated formula to search for certain data

[Bartosz Szymański]

Hi guys,

I have a big problem, I'm on internsip and I've gotten a task to develop an excel file which will search the data base and take out the names of companys that stopped making transactions. For instance if one company made a transaction in 1,2,3 month and stopped in 4th I would like the excel to automaticlly show up only these companies. I tried to do it by pivot tables but it's imposible to search indyvidually in so much data, and my task was to automatize that. My other idea was to develop an complicated IF function, for instance IF B2>=1 than true and B2<1 than false and include every of 6 months in formula so in the end we might get a diffrent score(either 1 or 0) if you get 1 it means that company made transaction every month and if we get 0 company stopped making transaction at some point. I would appreciate any help, any suggestions! Lookin a likes tables included in photos bellow. Second one if how my database look like, and first is copyied pivot tables to normal sheet that executes formulas. Regards      

[#2]

Hi @Bartosz Szymański,

I just saw your sheet. You want to know the which company stopped transaction.

I need some clarification. Then only i can solve. 

1.Company name column not found in excel (if confidential, please specify which column will come as result).

2. For your result, i've to take month and company name but company name not found. Can i take customer column for finding result.

3. Give one manual answer that you have found.

4. In Pivot, 1 to 6 representing months and 1 to 87 representing what company name.

5. Is that for any type of currency or specific currency only.

[#3]

Hey, @Logaraj Sekar
1. Company name is the number in "Customer" column.
2. Exactly
3. For instance Customer number 1 made transactions in 1 and 3 month but he was inactive in the other months so he should be shown in output
4. Yep
5. Any kind, its about finding out about which clients might be disapointed with our services and for that reason they might stopped dealing with us.
When I resolve this main issue I'll be thinking about how to apply some statistical models to that dat, and how I can divide this non active clients into groups.
Big Thanks for your answer. Kindest regards!

[#4]

Actually, it doesnt have to be resolved in the way i described it. Every way that resolves problem and gives me customers that stopped makin transaction will be appreciated :)

[#5]

@Logaraj Sekar

maybe I should try somehow apply a frequency function?

[#6]

 Hi @Bartosz Szymański,

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

[#7]

@Martijn Kersten van Dijk

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!!

[#8]

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.

[#9]

Hi,

@Sergei Baklan 

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!  

[#10]

Hi @Bartosz Szymański,

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.

[#11]

I copied exactly your formula, and I also wrote a date in U1 :)
=JEŻELI(SUMA.ILOCZYNÓW(($D$2:$D$82=$D73)*($A$2:$A$82>=($U$1-90)));"Pays"; "Didn't pay")
Excel automaticly changed your english formula, for one in mine version but it's the same.
I think that average would be better, but whatever easier to do I will be pleased with :)

Have a safe and nice flight Sergei!

[#12]

And I have another problem, how to aply on file like this frequency function, to find out if there are clients that for intance make transaction only on mndays or thursday :)

[#13]

Hi Bartosz,

Let do step by step, First, are you sure your formula

=JEŻELI(SUMA.ILOCZYNÓW(($D$2:$D$82=$D73)*($A$2:$A$82>=($U$1-90)));"Pays"; "Didn't pay")

is copied from the cell in row 73? Here it shall be =$D2 (in bold above) for row 2, =$D3 for row 3, etc.

Another point, instead of 

$D$2:$D$82 you shall use something like  $D$2:$D:300 or even better the $D:$D

The same is for $A2:$A$82 - it shall be the same range.

[#14]

if you could explain me ow exatlcy does your formula work, cause from I understood when sb "not pays" that mean that he didnt make any transaction in last 90 days, right?

BTW I'll check if your suggestions work when I'll get back to work tommorow, cause i dont have access to the file right now.

[#15]

Okay, how it works. I'll be based on my example, i.e. on formula in the helper cell for row #2.

=IF(SUMPRODUCT(($D$2:$D$82=$D2)*($A$2:$A$82>=($U$1-90))),"Pays", "Didn't pay")

First in SUMPRODUCT

($D$2:$D$82=$D2)

takes your entire range from second to 82nd rows and compare with D2. If it match it returns 1 (TRUE), othwervise FALSE (0). As result you have an array like {1,0,0,...1,..}

Second expression

($A$2:$A$82>=($U$1-90)

returns for the same range an array where the payment was done within last 90 days (TRUE/1) or not (FALSE/0). As the result an array like {0,0,...1,...}.

After that SUMPRODUCT do exactly what it name means, i.e. sum of these two arrays product. In product of these arrays you have 1 where the customer is the same as in D2 and payment was done within lates 90 days; otherwise 0. Resulting array before sum will be like {0,0,...1,0,...}. The sum on it returns how many times the customer in D2 paid within latest 90 days.

When you repeat the same for every row, i.e. for customers in D3, D4, etc. If number of payments was >0 for the customer in the row (TRUE) you assign the value "Paid", othrewise (FALSE) "Didn't pay".

Not sure my explanantion is clear enough. You may check how SUMPRODUCT works for example here https://exceljet.net/excel-functions/excel-sumproduct-function

[#16]

Next step - better to convert your data range into the table (if you are on Excel 2007 or later). When you may use structured refrences and don't depend on concrete range size when it changes. Select your data and in Ribbon->Home->Format as table. Our helper formula will be now

=IF(SUMPRODUCT(([Customer]=[@Customer])*([Value Date]>=($U$1-90))),"Pays", "Didn't pay")

see Table tab in the attached file

[#17]

Ok great, so formula is doing exatcly what I need. But it seems to still doesnt work on my real data. Im uploading a real file in which it should work.

For instance look at client 34652 he made transactions every month and it says "didnt pay" as his answer 

[#18]

Hi Bartosz,

Okay, i'll check bit later today, will be back then

[#19]

Sorry for the trouble, I forgot to adjust formula to cell number :) it works now thank you!
Just one moge question do you maybe know how to find out about frequency? if I would like to check if there is a pattern about on which day the customer usually makes transactions?

[#20]

Okay, good. On your big file performance could be an issue with current formula. I could play with that, but need to know on which version of Excel you are (2007, 2010, 2013 or 2016) assuming you are on Windows desktop.

As for the frequency the simplest way is to add one more column with day of month

=DAY(<date>)

and after that pivot your table with Customers names in rows, Days in columns and Count of customer names in Values.