Summing multiple columns where ID is the same

H

henry16

New Member
Joined
Jan 23, 2019
Messages
3
I'm trying to sum multiple columns if the first column is the same. I was trying to use the consolidate function but I also need the order date and customer name to remain (it changes if I consolidate).
Here's an example of an input:
V4U5z.jpg

Here's what I'd like it to output:
vNOMR.png

The number of rows will be dynamic but the number of columns will be static. It can output right over the existing data or under the last row. I have scripting to copy the resulting cells into another sheet.

Sorry my code that I've been banging my head on is on my work computer but my while loops kept getting hung until I hit Esc to stop the macro then it all magically calculates properly. I am hoping there are more elegant solutions than the one I've been working on.
Thanks in advance for your time and help!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
you can try PowerQuery aka Get&Transform

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type date}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Column2", "Column3"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Column1", "Column2", "Column3"}, {{"Sum1", each List.Sum([Column4]), type number}, {"Sum2", each List.Sum([Column5]), type number}, {"Sum3", each List.Sum([Column6]), type number}})
in
    #"Grouped Rows"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td=bgcolor:#5B9BD5]Column3[/td][td=bgcolor:#5B9BD5]Column4[/td][td=bgcolor:#5B9BD5]Column5[/td][td=bgcolor:#5B9BD5]Column6[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column2[/td][td=bgcolor:#70AD47]Column3[/td][td=bgcolor:#70AD47]Sum1[/td][td=bgcolor:#70AD47]Sum2[/td][td=bgcolor:#70AD47]Sum3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1001​
[/td][td=bgcolor:#DDEBF7]
15/01/2019​
[/td][td=bgcolor:#DDEBF7]Jim[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]
1001​
[/td][td=bgcolor:#E2EFDA]
15/01/2019​
[/td][td=bgcolor:#E2EFDA]Jim[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1002​
[/td][td]
15/01/2019​
[/td][td]Bob[/td][td][/td][td][/td][td][/td][td][/td][td]
1002​
[/td][td]
15/01/2019​
[/td][td]Bob[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1003​
[/td][td=bgcolor:#DDEBF7]
16/01/2019​
[/td][td=bgcolor:#DDEBF7]Tom[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]
1003​
[/td][td=bgcolor:#E2EFDA]
16/01/2019​
[/td][td=bgcolor:#E2EFDA]Tom[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1003​
[/td][td][/td][td][/td][td][/td][td][/td][td]
1​
[/td][td][/td][td]
1004​
[/td][td]
17/01/2019​
[/td][td]Paul[/td][td]
3​
[/td][td]
1​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1003​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1003​
[/td][td][/td][td][/td][td][/td][td][/td][td]
3​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1004​
[/td][td=bgcolor:#DDEBF7]
17/01/2019​
[/td][td=bgcolor:#DDEBF7]Paul[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1004​
[/td][td][/td][td][/td][td]
2​
[/td][td]
1​
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
Try this:-
NB:- Data assumed to start "A2".
NB:- This code will alter your data.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG23Jan17
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
    [COLOR="Navy"]For[/COLOR] Ac = 3 To 5
        .Item(Dn.Value).Offset(, Ac) = .Item(Dn.Value).Offset(, Ac) _
        + Dn.Offset(, Ac)
        .Item(Dn.Value).Offset(, Ac) = IIf(.Item(Dn.Value).Offset(, Ac) = 0, _
        "", .Item(Dn.Value).Offset(, Ac))
    [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]

[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
MickG said:
Try this:-
NB:- Data assumed to start "A2".
NB:- This code will alter your data.
Code: 
[COLOR=Navy]Sub[/COLOR] MG23Jan17
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] nRng [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR=Navy]Else[/COLOR]
    [COLOR=Navy]For[/COLOR] Ac = 3 To 5
        .Item(Dn.Value).Offset(, Ac) = .Item(Dn.Value).Offset(, Ac) _
        + Dn.Offset(, Ac)
        .Item(Dn.Value).Offset(, Ac) = IIf(.Item(Dn.Value).Offset(, Ac) = 0, _
        "", .Item(Dn.Value).Offset(, Ac))
    [COLOR=Navy]Next[/COLOR] Ac
        [COLOR=Navy]If[/COLOR] nRng [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR] [COLOR=Navy]Set[/COLOR] nRng = Dn Else [COLOR=Navy]Set[/COLOR] nRng = Union(nRng, Dn)
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]

[COLOR=Navy]If[/COLOR] Not nRng [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR] nRng.EntireRow.Delete
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...

Dangit, that works amazingly! Thank you Mick!!! Man it amazes me how simple the code is compared to what I was trying, AND IT WORKS!! Thank you again for your time and quick reply, hope you have a wonderful day!:cool:
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
[Excel 365] Maximizing unique combinations of words - No repeats between any two words
Replies
4
Views
349
waffle
W
M
  • Question Question
Stack/Combine multiple columns into one column
Replies
4
Views
326
myall_blues
myall_blues
S
  • Question Question
Sum Subtotals Based on numbered subsections
2
Replies
11
Views
593
sameri
S
L
  • Question Question
SUM except the strikethrough numbers? - NON_VBA
Replies
8
Views
648
_Landon
L
K
  • Solved
Need help consolidating data from 3 columns. I have a VBA code for QTY but the last column will now be data instead, how do I change code?
2
Replies
10
Views
838
rlv01
R

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top