Calculate Total Quantity

[Bindi_Baji]

Hi all,
I'm hoping somebody could offer a suggestion for a method to calculate the total order amount of a given item within VBA.
Basically, we have numerous items which need to be worked through to figure our the total order for each item. The issue I'm having is because of the way our stock system exports the data.
Please see below example of one item:
(cant install mini sheet on work laptop unfortunately.)

Essentially, the total order for Item 10001 should be 120 + 100 + 75 + 80 + 100 + 120 + 260 = 855

I hoping to find a method of calculating this on the fly for each item in a VBA procedure but having real trouble getting my head round the calculations.
We have hundreds of different customers so unfortunately I'm not sure filtering on these would be effective.

Any help would be gratefully received

 

[jkpieterse]

I have a hard time understanding how to go from the data table to the calculated example you show, the numbers do not appear to match up?

 

[Joe4]

I have a hard time understanding how to go from the data table to the calculated example you show, the numbers do not appear to match up?

It appears to me that they are wanting to sum up the "Total Order" column, when you remove all duplicates (based just on the "Custmer" and "Total Order" columns).

 

[Bindi_Baji]

It appears to me that they are wanting to sum up the "Total Order" column, when you remove all duplicates (based just on the "Custmer" and "Total Order" columns).

Essentially, yes.
So as an example, Cust2 has ordered 100 units of Item 10001.
This is picked from 3 sites in 40 + 40 + 20 but our system doesn't amend the Total Order column.
Cust2 gets what they want (100 units) but system seems to suggest the Total Order is 300 units.

 

[Fluff]

Hi & welcome to MrExcel.
You can do that with a formula, if your interested.

Fluff.xlsm
	A	B	C	D	E	F
1
2	10001	Cust1		120	10001	855
3	10001	Cust2		100
4	10001	Cust2		100
5	10001	Cust2		100
6	10001	Cust3		75
7	10001	Cust3		75
8	10001	Cust4		80
9	10001	Cust5		100
10	10001	Cust5		100
11	10001	Cust6		120
12	10001	Cust7		260
Data

Cell Formulas
Range	Formula
F2	F2	=LET(f,FILTER(B2:D1000,A2:A1000=E2),r,ROWS(f),c,INDEX(f,,1),SUM(FILTER(INDEX(f,,3),MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(c=TRANSPOSE(c)),SEQUENCE(r,,,0))=1)))