Difference in Readings(Column ), Considering similar IDs in Column A and different dates

[Anko_lee]

from Column A have Unique IDs but repeated with entries in Column B where we have dates and new readings in column C, I want in Coumn D to get difference in reading between current date and previous of similar ID, how should I do that and retain enteries in cells in Column D in every new entry?

[Glenn Kennedy]

Maybe this, in D2, copied down:

=IFERROR(C2-LOOKUP(2,1/($A$1:$A1=A2),$C$1:C1),"First Reading")

[Anko_lee]

Maybe this, in D2, copied down:

=IFERROR(C2-LOOKUP(2,1/($A$1:$A1=A2),$C$1:C1),"First Reading")

Thanks Glen, how do I go about it considering dates instead?

[Glenn Kennedy]

Post a sample sheet, showing manually calculated expected results.

[Anko_lee]

Kindly Find attached Will Highly appreciate.

[Glenn Kennedy]

Your attachment didn't attach. have another go!

[Anko_lee]

Find attached Sample

[Glenn Kennedy]

Why are D2 to D4 blank?


In your first sample, the dates were all in ascending order. Now they are not. Please clarify, can a situation like rows 46 & 47 are in descending date order, whereas they are ascending everywhere else?

[Glenn Kennedy]

I get it now!!

=IFERROR(C2-AGGREGATE(14,6,$C$2:$C$47/(($A$2:$A$47=A2)*($B$2:$B$47<B2)),1),"First Reading")

see sheet.

[Anko_lee]

Thanks Glen its truly working as I wanted!!!

[Glenn Kennedy]

Thate's because I posted your original sheet....

try now.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.