Formula to find last occurence of revision number to automate

Hi all,

I've scoured the web as much as possible and I am coming up without any solution. Either I'm thinking too hard or not enough.

Basically, for some reason, we are using Excel to manage a repository of requirements and it is somewhat nightmarish. I am trying to clean it up and make it as easy to use as I can possibly manage.

One item I'm trying to tackle is auto-numbering the revisions of requirements. That is, if a requirement ID is in the column more than once the revision number will add 1 to the previous revision number for the same ID.

I can easily get a count of how many times a requirement ID is in the column through COUNTIF and then everything starts to fall apart from there as I then try and get the last instance of that ID and it's revision number to add 1 to that. Using a VLOOKUP can get the revision number connected to the ID, but trying to do the last one of that is the spanner I can't fix.

Any solutions through use of formulas? I'm trying to avoid VBA where I can.

In summary, what I need is a formula that will:
- If the ID value has only a count of 1 then the revision number in the correct cell will be automatically entered as 001
-- else if the ID value has a count of >= 2 then find the last revision number for that ID and add a 1 to the number

Thanks.