Increment the value of an alphanumeric field by one if that combination already exists

Hello,

I am trying to work out a formula that returns an alphanumeric field incremented by one if that same combination of text and number exists in a previous cell.

In the attached file, I have to modify the formula in column B but do not know how.

Currently cells in column B return per each row a result based on content of the cell in column A. If A2 is the first cell in column A to contain "Risk", B2 will contain "R"&1="R1". However if I have another cell after A2 that contains "Risk" again, I would like the corresponding cell in column B to contain "R2" or "R[n+1]" if "Risk" already appears n times in previous cells of the same column B.

Same for other possible values of column A. As another example, if A25 is the first cell to contain "Dependency", B25 will contain "D"&1="D1". However if I have another cell after A25 that contains "Dependency" again, I would like the corresponding cell in column B to contain "D2" or "D[n+1]" if "Dependency" already appears n times in previous cells of the same column B.

RAID Log2.xlsx

Hi,

Check formula in attached spreadsheet.

Build a lookup table for converting the words to codes, in say Q2:R6
Q2:Q6 = Risk, Assumtion, Issue etc.
R2:R6 = R, A, I etc..

Then use
=VLOOKUP(A2,$Q$2:$R$6,2,FALSE)&COUNTIF(A$2:A2,A2)

Thank you so much pepe74287, works wonderfully!

Thank you anyways for your reply Jonmo1!

Thanks a Ton pepe74287! Worked like a charm