Need help with this

[sam singha]

Hi Friends,

I have attached a file which is a partly modified list of customers and some of relevant invoice details. I am trying to find the frequency of order of a particular customer viz if a customer is ordering monthly, quarterly, once in six months or an year or there is no pattern. what could be the best way to do it by using the invoice date field? This can help us understand a pattern in their purchase and forecast of sales. Any suggestion will be of help. Thank you all.

 

[Sandeep Badwal]

Hi Sam,

I have a look on your attached file. Your problem can be solved by using PivotTable tool. This is an easy solution without using any typical codes and formulas. I have provided a solution for that. Please check sheet no. 2 for your reference. It will provide you all details like invoice no., invoice date, amount and subtotal of amount for each and every record and also the pattern of purchase.

Hope, it will help.

 

[bines53]

Hi,

Check the file, if that's what you mean.

David

 

[sam singha]

Thank you David.
But I wanted more customer specific report. In the first column i need the customer name and in the second I need his approximate frequency/regularity of order. Ex. Akash Pharkacy has ordered 15 times so far and almost each month so I can consider and count on him as a every month customer. Adipia enterprise has ordered 8 times and most cases every second month, so on and so forth. I want not to do it manually by pivoting then filtering each name rather i want to some function to calculate this based on a decided criteria. I remember there is a function called bin and frequency which give this kind of result. I just dont know how to use it here. I have attached the file to make it clearer.
Thanks again for your reply.

Hi ,

Check the file, if that's what you mean.

David

 

[xlstime]

Hi Sam,

This could be a lengthy process to track every transaction to next translation age, to identify this you have check track every next transaction date and then calculate age between dates. if you want to find out first transaction v/s first repeat then you can refer enclosed file.

thnks

Hi Friends,

I have attached a file which is a partly modified list of customers and some of relevant invoice details. I am trying to find the frequency of order of a particular customer viz if a customer is ordering monthly, quarterly, once in six months or an year or there is no pattern. what could be the best way to do it by using the invoice date field? This can help us understand a pattern in their purchase and forecast of sales. Any suggestion will be of help. Thank you all.

 

[xlstime]

Oops attachment missing -

Hi Sam,

This could be a lengthy process to track every transaction to next translation age, to identify this you have check track every next transaction date and then calculate age between dates. if you want to find out first transaction v/s first repeat then you can refer enclosed file.

thnks

p

 

[sam singha]

Hey Sandeep,

Thanks for your reply and solution.
you are right it is useful in one way however i wanted something like this as attached. " i need the customer name and in the second I need his approximate frequency/regularity of order. Ex. Akash Pharkacy has ordered 15 times so far and almost each month so I can consider and count on him as a every month customer. Adipia enterprise has ordered 8 times and most cases every second month, so on and so forth. I want not to do it manually by pivoting then filtering each name rather i want to some function to calculate this based on a decided criteria. I remember there is a function called bin and frequency which give this kind of result. "

Hi Sam,

I have a look on your attached file. Your problem can be solved by using PivotTable tool. This is an easy solution without using any typical codes and formulas. I have provided a solution for that. Please check sheet no. 2 for your reference. It will provide you all details like invoice no., invoice date, amount and subtotal of amount for each and every record and also the pattern of purchase.

Hope, it will help.

 

[bines53]

Hi Sam,

Excel functions are missing some very basic in my opinion, one of them is called Unique,
I use this lack, by PLUG IN(add-in) to Excel, if you want to progress you have to download it, if not I retire, and be content with what they have.

David

 

[narunfca]

Hey Sandeep,

Thanks for your reply and solution.
you are right it is useful in one way however i wanted something like this as attached. " i need the customer name and in the second I need his approximate frequency/regularity of order. Ex. Akash Pharkacy has ordered 15 times so far and almost each month so I can consider and count on him as a every month customer. Adipia enterprise has ordered 8 times and most cases every second month, so on and so forth. I want not to do it manually by pivoting then filtering each name rather i want to some function to calculate this based on a decided criteria. I remember there is a function called bin and frequency which give this kind of result. "

I have enclosed the solution. Hope this will help.

with best regards

Arun N

 

[sam singha]

Thanks Arun! for taking out the time to figure it out and reply. Your formula is complex and gives an error when the occurence of order is one but is definitely helpful. I am closer to how i wanted it. thanks again.

-shyamal

 

[narunfca]

Hi

Modified the Formula. Hope this will Help.

with best regards
Arun N

 

[sam singha]

Thank you!!

Hi

Modified the Formula. Hope this will Help.

with best regards
Arun N[/quote