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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
404
waffle
W
M
  • Solved
Sum the values between two specified column headings - number of columns between will change
2
Replies
14
Views
304
Moley84
M
M
  • Question Question
Stack/Combine multiple columns into one column
Replies
4
Views
351
myall_blues
myall_blues
S
  • Question Question
Sum Subtotals Based on numbered subsections
2
Replies
11
Views
603
sameri
S
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
284
Pingaware
P

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

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