Consolidating rows based on 1 value and SUM the other

[Romano_odK]

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.

CODE	Artcode	Omschrijving	Verkocht2017	Factor	Totaal	Jaar2017
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​

 

[Peter_SSs]

So, could you post the expected results for that sample data?

 

[Romano_odK]

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​

 

[Peter_SSs]

Thanks (But don't put the table in formula tags. )

Try this

22 10 13.xlsm
	A					F		H	I	J
1	CODE					Totaal			CODE	Totaal
2	K205					66000			K205	192000
3	K205					126000			M2081	154000
4	M2081					81000			R10x5	57000
5	M2081					73000			R12x1	33000
6	R10x5					6000			R12x3	30000
7	R10x5					51000			R12x5	14000
8	R12x1					3000			R15x1	131000
9	R12x1					30000			R15x2	123000
10	R12x3					4000			R15x3	112000
11	R12x3					26000			R15x4	92000
12	R12x5					4000			R15x5	60000
13	R12x5					10000			R17x1	12000
14	R15x1					5000			R17x2	33000
15	R15x1					126000			R17x3	46000
16	R15x2					2000			R17x4	45000
17	R15x2					121000			R17x5	26000
18	R15x3					3000			R18x1	31000
19	R15x3					2000			R18x2	34000
20	R15x3					107000
21	R15x4					13000
22	R15x4					2000
23	R15x4					77000
24	R15x5					2000
25	R15x5					58000
26	R17x1					1000
27	R17x1					11000
28	R17x2					5000
29	R17x2					28000
30	R17x3					3000
31	R17x3					43000
32	R17x4					1000
33	R17x4					44000
34	R17x5					2000
35	R17x5					24000
36	R18x1					1000
37	R18x1					30000
38	R18x2					2000
39	R18x2					32000
Romano_odK

Cell Formulas
Range	Formula
I2:I19	I2	=UNIQUE(A2:A39)
J2:J19	J2	=SUMIFS(F2:F39,A2:A39,I2#)
Dynamic array formulas.

 

[Romano_odK]

Thanks (But don't put the table in formula tags. )

Try this

22 10 13.xlsm
	A					F		H	I	J
1	CODE					Totaal			CODE	Totaal
2	K205					66000			K205	192000
3	K205					126000			M2081	154000
4	M2081					81000			R10x5	57000
5	M2081					73000			R12x1	33000
6	R10x5					6000			R12x3	30000
7	R10x5					51000			R12x5	14000
8	R12x1					3000			R15x1	131000
9	R12x1					30000			R15x2	123000
10	R12x3					4000			R15x3	112000
11	R12x3					26000			R15x4	92000
12	R12x5					4000			R15x5	60000
13	R12x5					10000			R17x1	12000
14	R15x1					5000			R17x2	33000
15	R15x1					126000			R17x3	46000
16	R15x2					2000			R17x4	45000
17	R15x2					121000			R17x5	26000
18	R15x3					3000			R18x1	31000
19	R15x3					2000			R18x2	34000
20	R15x3					107000
21	R15x4					13000
22	R15x4					2000
23	R15x4					77000
24	R15x5					2000
25	R15x5					58000
26	R17x1					1000
27	R17x1					11000
28	R17x2					5000
29	R17x2					28000
30	R17x3					3000
31	R17x3					43000
32	R17x4					1000
33	R17x4					44000
34	R17x5					2000
35	R17x5					24000
36	R18x1					1000
37	R18x1					30000
38	R18x2					2000
39	R18x2					32000
Romano_odK

Cell Formulas
Range	Formula
I2:I19	I2	=UNIQUE(A2:A39)
J2:J19	J2	=SUMIFS(F2:F39,A2:A39,I2#)
Dynamic array formulas.

 

[Romano_odK]

Oops, sorry I won't do that again. Thank you it works as I hoped for.

Enjoy your day,

Romano

 

[Automatrix]

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

 

[Peter_SSs]

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
	A					F		H	I	J
1	CODE					Totaal			Row Labels	Sum of Totaal
2	K205					66000			K205	192,000
3	K205					126000			M2081	154,000
4	M2081					81000			R10x5	57,000
5	M2081					73000			R12x1	33,000
6	R10x5					6000			R12x3	30,000
7	R10x5					51000			R12x5	14,000
8	R12x1					3000			R15x1	131,000
9	R12x1					30000			R15x2	123,000
10	R12x3					4000			R15x3	112,000
11	R12x3					26000			R15x4	92,000
12	R12x5					4000			R15x5	60,000
13	R12x5					10000			R17x1	12,000
14	R15x1					5000			R17x2	33,000
15	R15x1					126000			R17x3	46,000
16	R15x2					2000			R17x4	45,000
17	R15x2					121000			R17x5	26,000
18	R15x3					3000			R18x1	31,000
19	R15x3					2000			R18x2	34,000
20	R15x3					107000			Grand Total	1,225,000
21	R15x4					13000
22	R15x4					2000
23	R15x4					77000
24	R15x5					2000
25	R15x5					58000
26	R17x1					1000
27	R17x1					11000
28	R17x2					5000
29	R17x2					28000
30	R17x3					3000
31	R17x3					43000
32	R17x4					1000
33	R17x4					44000
34	R17x5					2000
35	R17x5					24000
36	R18x1					1000
37	R18x1					30000
38	R18x2					2000
39	R18x2					32000
40
Romano_odK (2)