Data required to be compiled and added up, maybe INDEX?

[Scott Woody]

Hi there! Sorry I'm not very good at Excel and have been struggling on this for a while - I've tried so many methods and tried hard to search for an answer..

Basically I'm creating a document to help me to do my job better and I am very limited to what I can do.

The report I require the data from is like follows
[TABLE="width: 500"]
<tbody>[TR]
[TD]11111
[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]11112
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11114[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]11111
[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

I'm looking to basically paste this report into a pre-made workbook every week and it will update my user sheet, I require it to add up all the values for each row.

e.g. 11111 is 10

Thank you in advance for any help and apologies if I have not explained it well enough!

 

[sandy666]

How about PowerQuery (Get&Transform)?

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Sum", each List.Sum([Column2]), type number}})
in
    #"Grouped Rows"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Sum[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

11111​

[/td][td=bgcolor:#DDEBF7]

4​

[/td][td][/td][td=bgcolor:#E2EFDA]

11111​

[/td][td=bgcolor:#E2EFDA]

10​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

11112​

[/td][td]

1​

[/td][td][/td][td]

11112​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

11111​

[/td][td=bgcolor:#DDEBF7]

2​

[/td][td][/td][td=bgcolor:#E2EFDA]

11114​

[/td][td=bgcolor:#E2EFDA]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

11114​

[/td][td]

6​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

11111​

[/td][td=bgcolor:#DDEBF7]

3​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

11111​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[DRSteele]

Welcome to the forum.

There are many ways to achieve this. One would be a PivotTable. Another would be formulas. One set uses the new dynamic arrays and spilling functions in Excel365. The other uses function SUMIFS, but you will have to type in each of the possible Identity terms to make it work, as in G2 through G4 here.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G	H
1	Identity	Value		Identity	sum of Value		Identity	sum of Value
2	11111	4		11111	10		11111	10
3	11112	1		11112	1		11112	1
4	11111	2		11114	6		11114	6
5	11114	6
6	11111	3
7	11111	1
Sheet10

Cell Formulas
Range	Formula
D2	=UNIQUE(A2:A7)
E2	=SUMIFS(B2:B7,A2:A7,D2#)
H2	=SUMIFS($B$2:$B$7,$A$2:$A$7,G2)
H3	=SUMIFS($B$2:$B$7,$A$2:$A$7,G3)
H4	=SUMIFS($B$2:$B$7,$A$2:$A$7,G4)

[/FONT]

 

[Scott Woody]

Thank you both for your replies - I will try them out as soon as I can