Calculate difference between 2 records in a group

A

Anna Deux

New Member
Joined
Jul 22, 2004
Messages
22
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
ABCDEF
1BinItemIDDateInQtyValue
2
3001
4001.14-Aug88.4123
56-Aug87.8156
68-Aug88.3189
710-Aug67.8207
8ItemTotal3032.3F7-F4=84
9
10BinTotal3032.3
11
12
13002
14002.16-Aug1313.3205
157-Aug911.2250
16ItemTotal2224.5F15-F14=45
17
18002.29-Aug55.8111
1910-Aug79.2124
2011-Aug88.3150
21ItemTotal2023.3F20-F18=39
22
23BinTotal4447.8
24
25GRANDTOTAL7480.1
Anh Tu
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Maybe --

=F7-INDIRECT("F"&MATCH(REPT("z",255),B$1:B7))

in F8, Copied to other cells as needed.
 
Upvote 0
For a VBA approach (and assuming your layout is the same as shown above) you could do it like this...
Code: 
Sub Test1()
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
End Sub
Hope it helps.
Dan
 
Upvote 0
Thanks a lot Just_Jon, your formula worked great!!

Dan:

Your code is gorgeous. It will save me a lot of time as that is a very big worksheet.

Again thank you for both your input.

Anna Deux
 
Upvote 0
You're most welcome. Thanks for the kind words.
Since your worksheet is very big you might want to use the code with these lines added.
Code: 
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 
Application.ScreenUpdating = True
End Sub
This should speed things up a bit.

Dan
 
Upvote 0
Hello Dan:

I really appreciated your help. Do you mind explaining how the code works?
I kind of understand it but not completely. I don't understand the use of Cells(i, 6)(2, 1) and Cells(i, 6).End(xlUp). And what are the purposes of Application.ScreenUpdating = False ? and =True?

Forgive me it you think I ask too much! I tried to figure it out by myself but couldn't.

Anna
 
Upvote 0
Hi Anna,
There's no such thing as asking too much. :biggrin:

How the code works is...
The variable j has been defined as the lowest cell in column F with data in it.

For i = j To 1 Step -1 means use i as a variable defined as "whichever row we're in right now", starting with j (lowest cell in column F with data in it), and work our way up to row 1. (Step -1 means go up by one row at a time)

Cells(i, 6) means the cell in the row we're in right now, in the 6th column (Column F).

Cells(i, 6)(2, 1) means the same as "Cells(i, 6).Offset(1)" - Namely, same column, one row down of Cells(i, 6).

Application.ScreenUpdating = False will make it so the screen does not update with every change, which eliminates the screen flicker and speeds up the process because the screen will not try to keep up. (Application.ScreenUpdating = True makes it so your changes show once it's all done.)

Does this help clear it up a bit?
Dan
 
Upvote 0
Will someone help me modify HalfAce's code? The code works well if an item ID has at least 2 records. The result will not be correct if there is only one record for an Item ID; the result should be 0 in this case.

Thanks in advance.

Anna
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Extracting Records Between Dates
Replies
1
Views
253
omagoodness
O
L
  • Question
COUNTIF between two values
Replies
6
Views
791
Lux Aeterna
L
B
  • Solved
Formula for Calculating Difference Between Cells
Replies
2
Views
522
Bazza06
B
montecarlo2012
  • Solved
vba=Loop through each row in the data range.(B2:F10000)
Replies
9
Views
2K
montecarlo2012
montecarlo2012
O
  • Question
Macro to calculate grouped investments values based on selected month
Replies
0
Views
493
OET1982
O

Forum statistics

Threads
1,215,140
Messages
6,123,267
Members
449,093
Latest member
Vincent Khandagale

Share this page

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