Comparing values by category

[digitalsilver]

Need some help here with creating a formula with which to check the if the individual quantities of each type of item is not less than its previous balance.

I'd like my table to look something like the following:

Category	Initial Count	Balance	Check
Type X	100	200	OK
Type Y	50	80	OK
Type Z	10	15	OK
Type X	250	300	OK
Type Z	18	20	OK
Type Y	70	90	Error

Problem is, I don't quite know how to write the formula for the logic check, such that whenever I check the initial count of an item type, it should always be at least equal to or more than the balance of the last occurrence of that item type.
In the example above, Type Y returns an error in the check, as it starts with 70 the second time, when it ended with a balance of 80.

I'm guessing I'll need to use an array to do the initial check of matching the categories in the first column to every other one, but I'm not quite sure how to do that.

Would really appreciate any help here, thanks!

[FDibbins]

Hi and welcome to the forum

Try this, copied down...

=IF(COUNTIF($A$2:A2,A2)=1,"",IF(VLOOKUP(A2,$A1:C$2,3,FALSE)>B2,"ERROR","OK"))

[digitalsilver]

Hi, thanks for the welcome and suggestion!

I'll give that a shot and see if it works. I tried it a bit but it didn't work the first time round, but that might be because the table was a little more complicated than the one I'd illustrated above. Will try to modify the formula provided and hope that works. :P

[digitalsilver]

Ah ok, just realised why it wasn't working (though I still don't know how to solve it)

It works great for the second instance of an item type, but once a third row of the same type showed up, it would only compare its count to the balance of the first instance, not any subsequent ones.
I'm not too familiar with VLOOKUP, but is it because that function only returns the first instance and not any others?

[samba_ravi]

=IF(COUNTIF($A$2:$A2,$A2)=1,"OK",IF(INDEX($A$2:$C$7,LARGE(INDEX(($A$2:$A2=$A2)*ROW(INDIRECT(1&":"&ROWS($B$2:$B2))),0),2),3)>B2,"Error","OK"))
assuming that the data was in "A2:D7". Try the above formula in cell no "D2" and copy the same towards down

[digitalsilver]

Looks like this works, thanks so much, both of you!

[FDibbins]

Happy to help and thanks for the feedback