How to write a formula

Hi,
I have a column that lists the names of pieces of equipment. These are not sorted in any order in this column (the sheet is sorted by the owner's name in another column). It includes, for example
A5
C1
A3
B2
A4
A1
C3
etc
Further down in this column I have the next available number for A, B & C. To date I have been manually updating this but I am sure there must be a way to write a formula that searches the column above and gives me the next number. Any suggestions?

Cheers

Henry

I have now uploaded a smaple version of the spreadsheet. The real one has about 20 columns. You can see my list of varous equipment numbers in column 2 then at the end of column 2 the list that I am currently manually updating of the next number to use. Hope this is clearer