Consolidating rows based on 1 value and SUM the other

Romano_odK

Active Member
Joined
Jun 4, 2020
Messages
380
Office Version
  1. 365
Platform
  1. Windows
Good morning,

I would like to consolidate lines in a table, I need the values in column F to be added up. The result should be column A and F with the new value, the rest I do not need.

Is this possible? Thank you in advance.


CODEArtcodeOmschrijvingVerkocht2017FactorTotaalJaar2017
K205
1122000​
Kunststof blokjes 50x20x5 mm spijkerbaar - doos/1000 stuks
66​
1000​
66000​
2017​
K205
1122001​
Kunststof blokjes 50x20x5 mm spijkerbaar - zakje/100 stuks
1260​
100​
126000​
2017​
M2081
1107000​
Meubelwig 100x20x8/1 mm zwart - doos/500 stuks
162​
500​
81000​
2017​
M2081
1121000​
Meubelwig 100x20x8/1 mm zwart - zakje/100 stuks
730​
100​
73000​
2017​
R10x5
1100013​
Kunststof Rasterblokje 10x5 mm groen - doos/1000 stuks
6​
1000​
6000​
2017​
R10x5
1117013​
Kunststof Rasterblokje 10x5 mm groen - zakje/100 stuks
510​
100​
51000​
2017​
R12x1
1100015​
Kunststof Rasterblokje 12x1 mm wit - doos/1000 stuks
3​
1000​
3000​
2017​
R12x1
1117015​
Kunststof Rasterblokje 12x1 mm wit - zakje/100 stuks
300​
100​
30000​
2017​
R12x3
1100017​
Kunststof Rasterblokje 12x3 mm rood - doos/1000 stuks
4​
1000​
4000​
2017​
R12x3
1117017​
Kunststof Rasterblokje 12x3 mm rood - zakje/100 stuks
260​
100​
26000​
2017​
R12x5
1100019​
Kunststof Rasterblokje 12x5 mm groen - doos/1000 stuks
4​
1000​
4000​
2017​
R12x5
1117019​
Kunststof Rasterblokje 12x5 mm groen - zakje/100 stuks
100​
100​
10000​
2017​
R15x1
1100020​
Kunststof Rasterblokje 15x1 mm wit - doos/1000 stuks
5​
1000​
5000​
2017​
R15x1
1117020​
Kunststof Rasterblokje 15x1 mm wit - zakje/100 stuks
1260​
100​
126000​
2017​
R15x2
1100021​
Kunststof Rasterblokje 15x2 mm blauw - doos/1000 stuks
2​
1000​
2000​
2017​
R15x2
1117022​
Kunststof Rasterblokje 15x2 mm blauw - zakje/100 stuks
1210​
100​
121000​
2017​
R15x3
1100022​
Kunststof Rasterblokje 15x3 mm rood - doos/1000 stuks
3​
1000​
3000​
2017​
R15x3
1101017​
Kunststof Rasterblokje 15x3 mm rood - doos/1000 stuks/zakjes
2​
1000​
2000​
2017​
R15x3
1117024​
Kunststof Rasterblokje 15x3 mm rood - zakje/100 stuks
1070​
100​
107000​
2017​
R15x4
1100023​
Kunststof Rasterblokje 15x4 mm geel - doos/1000 stuks
13​
1000​
13000​
2017​
R15x4
1101018​
Kunststof Rasterblokje 15x4 mm geel - doos/1000 stuks/zakjes
2​
1000​
2000​
2017​
R15x4
1117025​
Kunststof Rasterblokje 15x4 mm geel - zakje/100 stuks
770​
100​
77000​
2017​
R15x5
1100024​
Kunststof Rasterblokje 15x5 mm groen - doos/1000 stuks
2​
1000​
2000​
2017​
R15x5
1117026​
Kunststof Rasterblokje 15x5 mm groen - zakje/100 stuks
580​
100​
58000​
2017​
R17x1
1100025​
Kunststof Rasterblokje 17x1 mm wit - doos/1000 stuks
1​
1000​
1000​
2017​
R17x1
1117027​
Kunststof Rasterblokje 17x1 mm wit - zakje/100 stuks
110​
100​
11000​
2017​
R17x2
1100026​
Kunststof Rasterblokje 17x2 mm blauw - doos/1000 stuks
5​
1000​
5000​
2017​
R17x2
1117028​
Kunststof Rasterblokje 17x2 mm blauw - zakje/100 stuks
280​
100​
28000​
2017​
R17x3
1100027​
Kunststof Rasterblokje 17x3 mm rood - doos/1000 stuks
3​
1000​
3000​
2017​
R17x3
1117029​
Kunststof Rasterblokje 17x3 mm rood - zakje/100 stuks
430​
100​
43000​
2017​
R17x4
1100028​
Kunststof Rasterblokje 17x4 mm geel - doos/1000 stuks
1​
1000​
1000​
2017​
R17x4
1117030​
Kunststof Rasterblokje 17x4 mm geel - zakje/100 stuks
440​
100​
44000​
2017​
R17x5
1100029​
Kunststof Rasterblokje 17x5 mm groen - doos/1000 stuks
2​
1000​
2000​
2017​
R17x5
1117031​
Kunststof Rasterblokje 17x5 mm groen - zakje/100 stuks
240​
100​
24000​
2017​
R18x1
1100030​
Kunststof Rasterblokje 18x1 mm wit - doos/1000 stuks
1​
1000​
1000​
2017​
R18x1
1117032​
Kunststof Rasterblokje 18x1 mm wit - zakje/100 stuks
300​
100​
30000​
2017​
R18x2
1100031​
Kunststof Rasterblokje 18x2 mm blauw - doos/1000 stuks
2​
1000​
2000​
2017​
R18x2
1117033​
Kunststof Rasterblokje 18x2 mm blauw - zakje/100 stuks
320​
100​
32000​
2017​
 
Last edited by a moderator:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
So, could you post the expected results for that sample data?
 
Upvote 0
So, could you post the expected results for that sample data?


K205
192000​
M2081
154000​
R10x5
57000​
R12x1
37000​
R12x3
30000​
R12x5
14000​
R15x1
131000​
R15x2
123000​
R15x3
112000​
R15x4
92000​
R15x5
66000​
R17x1
12000​
R17x2
33000​
R17x3
46000​
R17x4
45000​
R17x5
26000​
R18x1
31000​
R18x2
34000​
 
Last edited by a moderator:
Upvote 0
Thanks (But don't put the table in formula tags. ;))

Try this

22 10 13.xlsm
AFHIJ
1CODETotaalCODETotaal
2K20566000K205192000
3K205126000M2081154000
4M208181000R10x557000
5M208173000R12x133000
6R10x56000R12x330000
7R10x551000R12x514000
8R12x13000R15x1131000
9R12x130000R15x2123000
10R12x34000R15x3112000
11R12x326000R15x492000
12R12x54000R15x560000
13R12x510000R17x112000
14R15x15000R17x233000
15R15x1126000R17x346000
16R15x22000R17x445000
17R15x2121000R17x526000
18R15x33000R18x131000
19R15x32000R18x234000
20R15x3107000
21R15x413000
22R15x42000
23R15x477000
24R15x52000
25R15x558000
26R17x11000
27R17x111000
28R17x25000
29R17x228000
30R17x33000
31R17x343000
32R17x41000
33R17x444000
34R17x52000
35R17x524000
36R18x11000
37R18x130000
38R18x22000
39R18x232000
Romano_odK
Cell Formulas
RangeFormula
I2:I19I2=UNIQUE(A2:A39)
J2:J19J2=SUMIFS(F2:F39,A2:A39,I2#)
Dynamic array formulas.
 
Upvote 0
Solution
Thanks (But don't put the table in formula tags. ;))

Try this

22 10 13.xlsm
AFHIJ
1CODETotaalCODETotaal
2K20566000K205192000
3K205126000M2081154000
4M208181000R10x557000
5M208173000R12x133000
6R10x56000R12x330000
7R10x551000R12x514000
8R12x13000R15x1131000
9R12x130000R15x2123000
10R12x34000R15x3112000
11R12x326000R15x492000
12R12x54000R15x560000
13R12x510000R17x112000
14R15x15000R17x233000
15R15x1126000R17x346000
16R15x22000R17x445000
17R15x2121000R17x526000
18R15x33000R18x131000
19R15x32000R18x234000
20R15x3107000
21R15x413000
22R15x42000
23R15x477000
24R15x52000
25R15x558000
26R17x11000
27R17x111000
28R17x25000
29R17x228000
30R17x33000
31R17x343000
32R17x41000
33R17x444000
34R17x52000
35R17x524000
36R18x11000
37R18x130000
38R18x22000
39R18x232000
Romano_odK
Cell Formulas
RangeFormula
I2:I19I2=UNIQUE(A2:A39)
J2:J19J2=SUMIFS(F2:F39,A2:A39,I2#)
Dynamic array formulas.
 
Upvote 0
Oops, sorry I won't do that again. Thank you it works as I hoped for.

Enjoy your day,

Romano
 
Upvote 0
Power Query alternative: Group By CODE, sum of Totaal.

1665658707893.png
 
Upvote 0
Thank you it works as I hoped for.
You're welcome.

Since @Automatrix has offered an alternative, yet another alternative would be to use Excel's built-in Pivot Table feature (on the Insert ribbon tab)

22 10 13.xlsm
AFHIJ
1CODETotaalRow LabelsSum of Totaal
2K20566000K205192,000
3K205126000M2081154,000
4M208181000R10x557,000
5M208173000R12x133,000
6R10x56000R12x330,000
7R10x551000R12x514,000
8R12x13000R15x1131,000
9R12x130000R15x2123,000
10R12x34000R15x3112,000
11R12x326000R15x492,000
12R12x54000R15x560,000
13R12x510000R17x112,000
14R15x15000R17x233,000
15R15x1126000R17x346,000
16R15x22000R17x445,000
17R15x2121000R17x526,000
18R15x33000R18x131,000
19R15x32000R18x234,000
20R15x3107000Grand Total1,225,000
21R15x413000
22R15x42000
23R15x477000
24R15x52000
25R15x558000
26R17x11000
27R17x111000
28R17x25000
29R17x228000
30R17x33000
31R17x343000
32R17x41000
33R17x444000
34R17x52000
35R17x524000
36R18x11000
37R18x130000
38R18x22000
39R18x232000
40
Romano_odK (2)
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

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