Calculate based on condition

[Junarkar]

Hi,

My requirement is little complex than the heading. Below is my Data;

Billing Date	Sales Person ID	Invoice No	Category Desc.	Classification	Article No	Sales Qty.	Net Sales
01-03-2020​	14456​	464193454​	Power	Accessory	102572​	1.00​	1.00​
01-03-2020​	14456​	464193454​	TV	Main Product	109567​	1.00​	1.00​
01-03-2020​	11112​	464193407​	Smart Phones		84271​	2.00​	1.00​
01-03-2020​	11112​	464193407​	Cover	Accessory	112015​	1.00​	1.00​
01-03-2020​	1254​	464193013​	Smart Phones	Main Product	109918​	1.00​	1.00​
01-03-2020​	166541​	464192891​	Home Appliances		84271​	3.00​	1.00​
01-03-2020​	166541​	464192891​	Power	Accessory	112015​	1.00​	1.00​
01-03-2020​	199864​	464191442​	Air Conditioners	Main Product	100449​	1.00​	1.00​
01-03-2020​		464191442​	Phones Mobile Accessories	Accessory	102596​	1.00​	1.00​
01-03-2020​		464191442​	Stand	Accessory	105176​	1.00​	1.00​
01-03-2020​		464193454​	TV	Main Product	109567​	1.00​	1.00​
01-03-2020​	88754​	464192891​	Home Appliances	Main Product	84271​	2.00​	1.00​
01-03-2020​	99854​	464193454​	Power	Accessory	102572​	4.00​	1.00​

And below is the result I am looking for;

Desired Result
Category	Main Product Sale	Accessory Sale	Accessory Sale %
TV	2​	1​	50%​
Smart Phones (OS Based)	3​	1​	33%​
Home Appliances	5​	1​	20%​
Air Conditioners	1​	1​	100%​

Below is the eligible Accessory list which if sold along with the main product (in the same invoice number) will be considered for calculating accessory sale.

Eligible Accessory List
Category	TV	Smart Phones	Home Appliances	Air Conditioners
Eligible Accessories	Power	Cover	Power	Power
	Stand	Storage		Stand
	Cables	Phones Mobile Accessories

Example;

First case from the Desired Result Table.

TV is sold in two invoices, which are, 928386907 & 928386907.

But in invoice number 928386907 there is no accessory sold. Because of that total TV sale is 2 and accessory sale is 1.

So intention is to find if an employee is trying to sell an accessory while selling a main product.

I don't know if this can be accomplished with advanced excel formula or with VBA.

Can someone through some light. It will be a great help. I'm attaching the sample file too.

Regards
Junarkar

 

[Junarkar]

Hi,

Am I posting in a wrong Forum. Because I am not sure if it is doable with formula or VBA.

Please extend help.

 

[Peter Bartholomew]

I think the situation is that the task requires substantial effort rather than being a matter of offering guidance. At first sight, it appears to be just about possible with formulas but it would be useful to be able to use Office 365 functions such as FILTER (though it is conceivable that SUMIFS working over the entire dataset might also produce the required measures). Otherwise one starts coding.

Add to that, the fact that the specification has ambiguities and I can well understand why you have yet to find any takers for the problem.
I started by producing a list of distinct invoice numbers and, for each, produced a list of main products and a second list of accessories. I then checked for eligibility and started encountering problems. Since an invoice can cover multiple main products, several of which have power accessories, how do I assign power accessories to a specific products? If an order is raised by more than one employee in collaboration, does an accessory sold by one count against a main product sold by the other?

You show results aggregated across orders by product type. How can you then derive information relating to an employee?

 

[Junarkar]

I think the situation is that the task requires substantial effort rather than being a matter of offering guidance. At first sight, it appears to be just about possible with formulas but it would be useful to be able to use Office 365 functions such as FILTER (though it is conceivable that SUMIFS working over the entire dataset might also produce the required measures). Otherwise one starts coding.

Add to that, the fact that the specification has ambiguities and I can well understand why you have yet to find any takers for the problem.
I started by producing a list of distinct invoice numbers and, for each, produced a list of main products and a second list of accessories. I then checked for eligibility and started encountering problems. Since an invoice can cover multiple main products, several of which have power accessories, how do I assign power accessories to a specific products? If an order is raised by more than one employee in collaboration, does an accessory sold by one count against a main product sold by the other?

You show results aggregated across orders by product type. How can you then derive information relating to an employee?

Thank you for your time and solution. I totally agree with you that the task is complicated and apologize that I find it difficult to explain in a
easy manner. I will go through my problem again and try to give a clear version.

Regards
Junarkar