Help with consolidation of groups and sum of each

[LambVBA]

This is my first post so please (kindly) let me know if I made an error in my question - and I apologize in advance if any are made.

I have 5 possible shifts (1-5) in this Shift column. For each Job Code in the 1st column I need to somehow merge each Job Code into 1 total 'qty' per each shift.

Here is a sample report of my 3 column table I am looking at:
[TABLE="class: grid, width: 283"]
<tbody>[TR]
[TD]Job Code[/TD]
[TD]qty[/TD]
[TD]Shift[/TD]
[/TR]
[TR]
[TD] Received[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] Received[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] Received[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] Received[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] Received[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD] Loaded[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD] Loaded[/TD]
[TD="align: right"]194[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD] Loaded[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] Loaded[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD] Picked[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD] Picked[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] Picked[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] Picked[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD] Picked[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD] Picked[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] Picked[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD] Picked[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD] Picked[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] Picked[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD] Replenished[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD] Replenished[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] Replenished[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] Replenished[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD] Replenished[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Cases Picked[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Cases Picked[/TD]
[TD="align: right"]875[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Cases Picked[/TD]
[TD="align: right"]431[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Transfer[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Transfer[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Transfer[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Transfer[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Transfer[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Putaway[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

Expected (hopeful) outcome below...
For instance the 'Loaded' Job Code would look like this:
[TABLE="class: grid, width: 283, align: left"]
<tbody>[TR]
[TD]Job Code[/TD]
[TD]qty[/TD]
[TD]Shift[/TD]
[/TR]
[TR]
[TD]Loaded[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Loaded[/TD]
[TD="align: right"]194[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Loaded[/TD]
[TD="align: right"]313[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[azumi]

Put in F2:

=SUMPRODUCT(--($A$2:$A$34=E2),--($C$2:$C$34=G2),($B$2:$B$34))

E2 = "Job Code"
G2 = "Shift"

Assumed your data in ranges A1:C34 (include column header) as your sample table above
Hope it helps

Regards

 

[Marcelo Branco]

Hi and welcome to Mr Excel

Try a Pivot Table

Row Labels
Job Code
Shift

Values area
Qty

Something like this


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Job Code​

[/TD]
[TD]

Shift​

[/TD]
[TD]

Sum of qty​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

Cases Picked​

[/TD]
[TD]

2​

[/TD]
[TD]

875​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

431​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

800​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

Cases Picked Total​

[/TD]
[TD][/TD]
[TD]

2106​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

Loaded​

[/TD]
[TD]

1​

[/TD]
[TD]

53​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

194​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

313​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

Loaded Total​

[/TD]
[TD][/TD]
[TD]

560​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

Picked​

[/TD]
[TD]

1​

[/TD]
[TD]

277​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

75​

[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

324​

[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

95​

[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

Picked Total​

[/TD]
[TD][/TD]
[TD]

771​

[/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]

Putaway​

[/TD]
[TD]

5​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]

Putaway Total​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]

Received​

[/TD]
[TD]

1​

[/TD]
[TD]

305​

[/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

27​

[/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]

Received Total​

[/TD]
[TD][/TD]
[TD]

332​

[/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]

Replenished​

[/TD]
[TD]

1​

[/TD]
[TD]

36​

[/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

29​

[/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]

Replenished Total​

[/TD]
[TD][/TD]
[TD]

65​

[/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD]

Transfer​

[/TD]
[TD]

1​

[/TD]
[TD]

149​

[/TD]
[/TR]
[TR]
[TD]

26​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

17​

[/TD]
[/TR]
[TR]
[TD]

27​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

104​

[/TD]
[/TR]
[TR]
[TD]

28​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

11​

[/TD]
[/TR]
[TR]
[TD]

29​

[/TD]
[TD]

Transfer Total​

[/TD]
[TD][/TD]
[TD]

281​

[/TD]
[/TR]
[TR]
[TD]

30​

[/TD]
[TD]

Grand Total​

[/TD]
[TD][/TD]
[TD]

4116​

[/TD]
[/TR]
</TBODY>[/TABLE]



Hope this helps

M.

 

[LambVBA]

Took me a minute to apply this (my lack of skill, not yours). Works great! Thank you very much!