Help with Duplicated/Repeated Orders and Values

[Tank2000]

Hi everyone.

Looking for some help. I have a spreadsheet that contains the following...

Column A has repeated Order Numbers
Column B has the values associated with those repeated Order Number

For example:

Column A
1234
1234
1234
1234
1234
1235
1235
1236
1237
1237
1237
1237
1237

Column B
100
100
100
100
100
200
200
300
400
400
400
400
400

Here's what I'm looking for...

Is there a formula that can look at the repeated Order Numbers in Column A and sum the value of all of those repeated Order Numbers and place that total beside the first Order number record in lets say Column C?

And another formula that can look at the repeated Order Numbers in Column A and sum the value of all of those repeated Order Numbers less one Order Number record (as the first record is a true transaction) place that total beside the first Order number record in lets say Column D?

So for Order Number 1234, Column C should have a total sum for that order number of 500 and Coulmn D should have a total sum of only 100.

Any help with crafting a formula would be greatly appreciated.

Cheers!

 

[Toadstool]

Hi Tank2000,

I'm not sure I've followed you on "sum the value of all of those repeated Order Numbers less one Order Number record (as the first record is a true transaction) place that total beside the first Order number record in lets say Column D" as it sounds like you just want the value of the first Order Number and not a subtraction?

Here's what I have so far:

Tank2000.xlsx
	A	B	C	D
1	Order Number	Value
2	1234	100	500	100
3	1234	100
4	1234	100
5	1234	100
6	1234	100
7	1235	200	400	200
8	1235	200
9	1236	300	300	300
10	1237	400	2000	400
11	1237	400
12	1237	400
13	1237	400
14	1237	400
Sheet1

Cell Formulas
Range	Formula
C2:C14	C2	=IF(COUNTIF($A$1:$A2,A2)=1,SUMIF($A$2:$A$14,A2,$B$2:$B$14),"")
D2:D14	D2	=IF(C2<>"",B2,"")

 

[Tank2000]

Thanks for the quick response Toadstool.

This is perfect!

Can I trouble you for one last formula? In Column E can you include the sum of the Order Number less the first record total? So for Order Number 1234, Column E should have a total sum for that order number of 400.

Cheers!

 

[Toadstool]

You're welcome!

So that would be...

Tank2000.xlsx
	A	B	C	D	E
1	Order Number	Value
2	1234	100	500	100	400
3	1234	100
4	1234	100
5	1234	100
6	1234	100
7	1235	200	400	200	200
8	1235	200
9	1236	300	300	300	0
10	1237	400	2000	400	1600
11	1237	400
12	1237	400
13	1237	400
14	1237	400
Sheet1

Cell Formulas
Range	Formula
C2:C14	C2	=IF(COUNTIF($A$1:$A2,A2)=1,SUMIF($A$2:$A$14,A2,$B$2:$B$14),"")
D2:D14	D2	=IF(C2<>"",B2,"")
E2:E14	E2	=IF(C2<>"",C2-B2,"")

 

[Tank2000]

Thanks again Toadstool.

I'll give this a try tomorrow at work and will then advise if this solved the problem.

Cheers!