Calculate difference between 2 records in a group

[Anna Deux]

I have a big spreadsheet of over 3000 records grouped by Bin then by Item ID that I import from a text document.
Each Item ID group will not have the same number of records and might have 100 records more or less. In all the rows that show "Item Total", in column F, I have to calculate the difference between the last record and the first record of each Item ID group (cells that I highlighted in green).
The result that I should get in cell F8 should be 84 (207-123=84), and 45 in cell F16, and 39 in F21.
Will you please help me with this? Any help would be greatly appreciated?

Anh Tu1.xls
	A	B	C	D	E	F
1	Bin	ItemID	DateIn	Qty	Value
2
3	001
4		001.1	4-Aug	8	8.4	123
5			6-Aug	8	7.8	156
6			8-Aug	8	8.3	189
7			10-Aug	6	7.8	207
8		ItemTotal		30	32.3	F7-F4=84
9
10	BinTotal			30	32.3
11
12
13	002
14		002.1	6-Aug	13	13.3	205
15			7-Aug	9	11.2	250
16		ItemTotal		22	24.5	F15-F14=45
17
18		002.2	9-Aug	5	5.8	111
19			10-Aug	7	9.2	124
20			11-Aug	8	8.3	150
21		ItemTotal		20	23.3	F20-F18=39
22
23	BinTotal			44	47.8
24
25	GRANDTOTAL			74	80.1
Anh Tu

 

[HalfAce]

Hi again Anna,
How about we run another loop to check for any value of less than or equal to zero?
Try replacing what you've got now with this.

Sub Test1()
Application.ScreenUpdating = False
j = Range("F65536").End(xlUp).Row

For i = j To 1 Step -1
If Cells(i, 6) <> "" And Cells(i + 1, 6) = "" Then Cells(i, 6)(2, 1) = Cells(i, 6) - Cells(i, 6).End(xlUp)
Next i

For i = 1 To j
If Not IsEmpty(Cells(i, 6)) And Cells(i, 6) <= 0 Then Cells(i, 6) = 0
Next i

Application.ScreenUpdating = True
End Sub

This any better?
Dan

 

[Anna Deux]

Hello Dan:

I modified the code but it does not work completely. There are several rows that gave wrong results. How can I post back so you can have a look what's wrong?

As always thanks a lot for your help

Anna

 

[Anna Deux]

I run the code that Half Ace modified but there are still some errors. Will you please help me solve it. There are errors where results should be negative numbers or 0. Your help would be very appreciated!

Bin Item ID Date In Qty Value

001
001.1 4-Aug 8 8.4 123
6-Aug 8 7.8 156
8-Aug 8 8.3 189
10-Aug 6 7.8 100
Item Total 30 32.3 0 Should be -13

001.2 4-Aug 7 9.2 200
Item Total 7 9.2 100 OK


Bin Total 37 41.5


002
002.1 6-Aug 13 13.3 205
7-Aug 9 11.2 250
Item Total 22 24.5 45 OK

002.2 9-Aug 5 5.8 111
Item Total 5 5.8 0 OK

Bin Total 27 30.3


003 003.1 9-Aug 5 5.8 111
10-Aug 7 9.2 124
11-Aug 8 8.3 150
12-Aug 6 10.5 160.0
Item Total 26 33.8 49.0 OK

003.2 9-Aug 5 5.8 123.0
Item Total 5 5.8 0.0 OK

003.3 10-Aug 7 9.2 111.0
11-Aug 8 8.3 125.0
12-Aug 6 10.5 101.0
Item Total 21 28.0 0.0 Wrong, must be -10

Bin Total 52 67.6


004 004.1 9-Aug 7 9.2 123.0
Item Total 7 9.2 22.0 wrong, must be 0

004.2 2-Aug 6 8.8 236.0
11-Aug 8 8.3 245.0
12-Aug 6 10.5 226.0
Item Total 20 27.6 -10.0 OK

Bin Total 27 36.8


Errors are on row 8 (should be -13), row 40 (should be -10), and row 46 (should be 0, - whenever an item ID has only 1 record, the result should be 0).

 

[Anna Deux]

Sorry, I copied the spreadsheet again with HTML. Any help would be appreciated.

Test1.xls
	A	B	C	D	E	F	G
1	Bin	ItemID	DateIn	Qty	Value
2	001
3		001.1	4-Aug	8	8.4	123
4			6-Aug	8	7.8	156
5		ItemTotal		30	32.3	33	OK
6
7		001.2	4-Aug	7	9.2	200
8		ItemTotal		7	9.2	44	Shouldbe0
9
10
11	BinTotal			37	41.5
12
13
14	002
15		002.1	6-Aug	13	13.3	205
16			7-Aug	9	11.2	250
17		ItemTotal		22	24.5	45	OK
18
19		002.2	9-Aug	5	5.8	111
20		ItemTotal		5	5.8	0	OK
21
22	BinTotal			27	30.3
23
24
25	003	003.1	9-Aug	5	5.8	111
26			10-Aug	7	9.2	100
27		ItemTotal		12	15.0	0.0	Shouldbe-11
28
29		003.2	9-Aug	5	5.8	123.0
30						23.0	Shouldbe0
Anh Tu