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

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

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:$DThe same is for $A2:$A$82 - it shall be the same range.

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

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

Complicated formula to search for certain data

29 Replies

Martijn Kersten van Dijk
Copper Contributor
Aug 02, 2017
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
przykladowy plik.xlsx26 KB
- Bartosz Szymański
  Brass Contributor
  Aug 02, 2017
  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!!
  - SergeiBaklan
    Diamond Contributor
    Aug 02, 2017
    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.
    przykladowy plik+1.xlsx24 KB
Logaraj Sekar
Iron Contributor
Jul 31, 2017
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.
- Bartosz Szymański
  Brass Contributor
  Aug 01, 2017
  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!
  - Bartosz Szymański
    Brass Contributor
    Aug 01, 2017
    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 :)

Forum Discussion

Complicated formula to search for certain data

29 Replies

Resources