Hi,
My requirement is little complex than the heading. Below is my Data;
And below is the result I am looking for;
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.
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
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