Need to find the total count of products order based on color and sizes

Hi ,

I am new to this forum and this is the first time that i am out online to check for a solution.

I handle a E-Commerce Business and at the stock level its very important for me to know once my team pulls the data from the panels as to how many Products are ordered for each color and sizes and then we see how much stock is available with us to dispatch.

The time consuming area is we have the product code or SKU code made available but not size and colors so based on the SKU we manually count and put them in the format required.
you can refer the "final report format" tab for the format required and for the data pulled from panels are put in " base file " tab and description of the SKU is given in the last tab FYR.

Regards,
Sudhina Binu

with the help of pivot we can make this report. in base file there are not some code so they are not showing when you put all code they will be show there.

Hi Kamboj,


Possibly may be those SKU are the combo requests place where two styles and two color combinations are requested which is a part of our challenge.We manualy add them to the respective color and to there size.

And Sir my request is to have them in final report format i need the color and size wise qty you can refer the first sheet as a reference.

But am happy that you gave me a hope of possibility.

Regards,
Sudhina Binu

Hi Kamboj,

Is their a way out.

Regards,
Sudhina Binu

Hi Sudhina,

Yes there is a solution of every problem matter is who find it.
Here is the file attached.

have your problem solved or not?

i Kamboj,

I was checking on the formula imputed.found its not picking the right product and color also for the SKU which is a combination of 3 to 4 colors and 2 different styles is not throwing up the combinations nor the count is getting added to the color and product.


The below is one example where its picked incorrect product,color and also its not showing the two style product ordered.

Incorrect one -

RCSH-B-RCPT-BE-M 1 Snapdeal.com Navy Blue Round Neck T-Shirts M


Correct one -

RCSH-B-RCPT-BE-M 1 Snapdeal.com Black /Beige Shirts/Polo T-Shirt M

I have updated comments for few in which i found the formulas is not working out,rest am still checking if the applied formulas is showing the output.In the last sheet i have highlighted the unique senarios to find out a solution.Also attached pending orders the final report FYR to show that the formula is not giving me those counts under each style when compared with the pivot.


Regards,
Sudhina Binu

in base file sku code should be as in sku description sheet and if you add another code then sort the data on code column. I have changed vlookup formula to exact match so if code match with the value then show its colour & product otherwise show blank.

Hi Sudhina Binu,
Have your stock report matching now?



In the event Code provided resolves your issue, please mark your Thread as SOLVED

Hey no it has not solved yet.


The below is one example where its picked incorrect product,color and also its not showing the two style product ordered.

Incorrect one -

RCSH-B-RCPT-BE-M 1 Snapdeal.com Navy Blue Round Neck T-Shirts M


Correct one -

RCSH-B-RCPT-BE-M 1 Snapdeal.com Black /Beige Shirts/Polo T-Shirt M

I have updated comments for few in which i found the formulas is not working out,rest am still checking if the applied formulas is showing the output.In the last sheet i have highlighted the unique senarios to find out a solution.Also attached pending orders the final report FYR to show that the formula is not giving me those counts under each style when compared with the pivot.


Regards,
Sudhina Binu

first you have to update your sku description sheet because in base file it pick all value from there secondly you have not seen the last updated file because the code RCSH-B-RCPT-BE-M is not there in the sku description sheet. if you want to add more in sku desc. then insert in rows before the last row so that it automatically increase the range of vlookup or you can manually increase the range as =IFERROR(VLOOKUP(A213,'SKU description'!$A$2:$C$ 96 , 3,FALSE),"") 96 is your last row.

Hi Kamboj,

I need your help badly for the attached i have updated the combo requests as product 1 & product 2 with their colors.How to manage
these because when a customer places order for a particular brand for 2 colors will their count get added.

The biggest challenge lies in combo requests where we also get SKU for 2 different brands and 2 different colors and sizes so will this
formula help address those as well.


Regards,
Sudhina Binu

but how can u differentiate that this sku code has more than 2 brand/ 2 colours, how computer know