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]

Have a look at SUMIFS
https://exceljet.net/excel-functions/excel-sumifs-function

 

[sandy666]

another way
with PivotTable

Date	Customer Name	Product Code	Qty		Sum of Qty
28/05/2019​	Cust2	Product1	1​			Cust1	Cust2	Cust3
10/06/2019​	Cust2	Product1	1​		Product1		10​
12/06/2019​	Cust1	Product2	5​		Product2	9​
14/06/2019​	Cust2	Product1	3​		Product4			11​
16/06/2019​	Cust1	Product2	4​
18/06/2019​	Cust2	Product1	5​
20/06/2019​	Cust3	Product4	11​

is that what you want?

 

[stavrosiona]

As in sheet 2 column A I want to write all the Products from Product 1 until product 100 and row A I want to write all the opticians name and not only customers that ordered products I.prefer not to be pivot table

Also becaue like the sheet 2 (which will had all the products and customer order) i will have another sheet 3 that has all the invoiced products in cystomer and a sheet 4 that will have the pending (sheet2 - sheet 3)
But first I want to be able in sheet 2 to read data from sheet 1 and add them in sheet 2 ( product order table correctly)

 

[Fluff]

Did you try the sumifs?

 

[sandy666]

product 100 and row A

You mean COLUMN A ?

the opticians name and not only customers that ordered products

I don't see any "opticians" in your source data
Post more representative example and expected result (from this data)

I.prefer not to be pivot table

ok, no problem. Have a nice day

 

[stavrosiona]

Hi Sandy666 sorry my mistake, I will try to be more cleared of what i want:
Lets say the exact what I want to do:
One sheet should have in ROW 1 all the customers: Customer1, Customer 2......
In Column A should have all the products: Product1, Product 2.....
This sheet should be named: Orders.
what I want to do is to have another sheet that I will add the orders:
and in Row 1 will have these details:
Date. customer Name. Product Code Qty

so every day when I receive an order i will add it in this sheet 2 i.e
Date. customer Name. Product Code Qty
28/05 Customer 1 Product5 1
29/05 Customer 3 Product4 1
10/06 Customer 1 Product 5 1

Then I want in the Sheet 1 where my table is to have the following:
for customer 1 in column B6 where is the product5 for customer 1 to be qty 2 which is the total qty a customer ordered all the dates.

orders	customer1	customer2	customer3	customer4	customer5	customer6	customer7	customer8	customer9	customer10
product1
product2
product3
product4			1
product5	2
product6
product7
product8
product9
product10

<colgroup><col><col><col span="5"><col><col><col><col></colgroup><tbody>
</tbody>

The same sheets will have for Deliveries and for Pending will have a sheet that will deduct from Order Sheet the Deliveries.
So if I am able to manage a sheet 2 to place the order and then to calculate and add for each customer and each product the quantities then will be great.

 

[stavrosiona]

Hi FLUFF sorry my mistake:
Lets say the exact what I want to do:
One sheet should have in ROW 1 all the customers: Customer1, Customer 2......
In Column A should have all the products: Product1, Product 2.....
This sheet should be named: Orders.
what I want to do is to have another sheet that I will add the orders:
and in Row 1 will have these details:
Date. customer Name. Product Code Qty

so every day when I receive an order i will add it in this sheet 2 i.e
Date. customer Name. Product Code Qty
28/05 Customer 1 Product5 1
29/05 Customer 3 Product4 1
10/06 Customer 1 Product 5 1

Then I want in the Sheet 1 where my table is to have the following:
for customer 1 in column B6 where is the product5 for customer 1 to be qty 2 which is the total qty a customer ordered all the dates.

orders	customer1	customer2	customer3	customer4	customer5	customer6	customer7	customer8	customer9	customer10
product1
product2
product3
product4			1
product5	2
product6
product7
product8
product9
product10

<tbody>
</tbody>

The same sheets will have for Deliveries and for Pending will have a sheet that will deduct from Order Sheet the Deliveries.
So if I am able to manage a sheet 2 to place the order and then to calculate and add for each customer and each product the quantities then will be great.

 

[stavrosiona]

Did you try the sumifs?

Hi I cannot manage to do it with Sumif
I have 2 sheets:
One that I place the orders:

Date Cust Name Product Code Qty

28/05 Customer1 Product5 1
10/06 customer 1 Product5 1


And one other sheet that has all the products in Column A and all the Customer in Row 1 and that will manage to calculate from sheet 2 and place the order quantities in the cell and rows for the selected product and customerlike the example that for Customer 1 the product5 has been order 2 quanities in total.

Customer 1 2 3 4 5 6

Product1
Product2
Product3
Product4
Product5 2
Product6

 

[sandy666]

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