Sales Order and Report Excel

[stavrosiona]

Hi I am creating an excel where in 1 sheet I will take lets say the order:
i.e:
Date, Customer Name, Product Code, Qty

and on the sheet 2 I will have on the A column all the products and on A2, A3,A4, A5 .. all the customers.

What I want to do is to be possible the Sheet 2 to read from Sheet 1 and do the following:
Add the qty order in sheet 1 for specific customer in the sheet 2 under the raw and line of the product for the specific customer.
Step 2 is in case a customer order today on the 28/05/2019 1 piece pf product 2 and after some days i.e 10/06/19 one more of the same product so the sheet 2 to do the ADD and show in sheet 2 under specific customer i.e Cust 2 the qty of Product1 to be 2.


Example f Sheet 1 :
Date, Customer Name, Product Code, Qty
28/05/19 Cust2 Product1 1
10/06/19 Cust2 Product1 1


See example of sheet 2 bellow:

Product Orders Cust1 Cust2 Cust 3 ...
Product 1 2
Product 2 1 1 1
Product 3 2 1
Product 4 3

 

[Fluff]

With sheet 1 like

Excel 2013/2016
	A	B	C	D
1	Date	Customer Name	Product Code	Qty
2	28/05/2019	Customer 1	Product 5	1
3	10/06/2019	Customer 2	Product 4	1
4	15/07/2019	Customer 1	Product 5	1
Sheet1

Shhet2

Excel 2013/2016
	A	B	C	D
1	Product Orders	Customer 1	Customer 2	Customer 3
2	Product 1	0	0	0
3	Product 2	0	0	0
4	Product 3	0	0	0
5	Product 4	0	1	0
6	Product 5	2	0	0
7	Product 6	0	0	0
8	Product 7	0	0	0
9	Product 8	0	0	0
10	Product 9	0	0	0
11	Product 10	0	0	0
12	Product 11	0	0	0
Sheet2

Cell Formulas
Range	Formula
B2	=SUMIFS(Sheet1!$D$2:$D$4,Sheet1!$C$2:$C$4,$A2,Sheet1!$B$2:$B$4,B$1)

 

[stavrosiona]

How about PowerQuery (Get&Transform) or you want to stay with formula(s)?

Hi as I want to be more easy for the rest people of the company I believed that formulas or VBA was better.
I do not kn ow how to use Power Query, if you can help me in either way will be great.
thanks

 

[sandy666]

you got formula solution from Fluff so it should be easy

edit:
anyway, what is your Excel version? and everyone there has the same version?

 

[stavrosiona]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]=SUMIFS(Sheet1!$D$2:$D$4,Sheet1!$C$2:$C$4,$A2,Sheet1!$B$2:$B$4,B$1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This solution seems to work.
Thanks,
What about if in row1 and coumn A instead of text has formulas that read all the customers from another sheet abd alll products from another sheet. Will the formula work or has to be text in column a and row 1 to work? I mean in row A3 insted of "Custoner2" I have =Sheet4!A5 which is the sheet that has in all coumn A all the customers.

 

[Fluff]

It should work quite happily if there is a formula in there rather than text

Also please do not quote entire posts, as it just clutters up the board