how do i compare rows within each group?

[seagreen]

Hello,
How would I calculate the value of colB[row2]-colB[row1] for a group of records/rows in Excel but only within that group of records? In other words, if a value in A changes, don't do the calculation between the first row of the new value and the last value of the previous value in A.

Ex:
ssn

1111111 89
1111111 98 9
1111111 55 -43


2222222 9
2222222 52 43
2222222 17 -35
2222222 33 16
2222222 10 -23

4444444 22
4444444 11 -11

Thanks for any help!

 

[Joe4]

Assuming your data is in columns A and B and starts on row 2, then enter this formula in C2 and copy down for all rows:

=IF(A2=A1,B2-B1,"")

 

[schielrn]

I'm not sure I completely underdtand what you are asking? Are there spaces between these groups I assume?

Why can you not use a simple subtraction formula subtracting the last 2 cells?

 

[seagreen]

Thanks Joe4, that was just what I needed! I had a mental block on the A2=A1 part.

 

[seagreen]

I'm not sure I completely underdtand what you are asking? Are there spaces between these groups I assume?

Why can you not use a simple subtraction formula subtracting the last 2 cells?

Thanks schielm, I have an increased row height between each group but I see what you mean about how this would work if I had a blank row instead.

 

[Joe4]

The formula I gave you will work whether or not you have blank rows between your data sets. If there is only one blank row, it will work just fine. If there is more than one, it will still work but return a 0 for each 2nd (or 3rd, etc) consecutive blank row.

If you had that situation, it could be easily rectified with a slight modification:

=IF(AND(LEN(A2)>0,A2=A1),B2-B1,"")

 

[seagreen]

The formula I gave you will work whether or not you have blank rows between your data sets. If there is only one blank row, it will work just fine. If there is more than one, it will still work but return a 0 for each 2nd (or 3rd, etc) consecutive blank row.

If you had that situation, it could be easily rectified with a slight modification:

=IF(AND(LEN(A2)>0,A2=A1),B2-B1,"")

Thanks Joe4. I don't have any blank rows. Your solution works regardless of whether there are blank rows as you said and I used it. When the other poster offered his solution, I had already used your solution but I saw how his solution would work if I had blank rows. Thanks again.