I have a list that looks like this in column B/C


1 Apple
2a Banana
2b Banana
3a Cherry
3b Cherry
3c Cherry
4 Durian
5 Elderberry
6a Fig
6b Fig

I want to create a formula in the adjacent column B that looks at the column C to create a numerical system such if there's only 1 item of it in the list, there's no "a", "b" suffix, but if there's multiple (like two bananas), it is the next number (i.e. 2 after the preceding 1) but adds the subtext of "a", "b" for however many instances.

I started with the following equation but can't figure out how to fix the part where it breaks at 3a after 2a/2b output. I think it has to do with the bolded part of the text needing to be ISNUMBER on the italic/underline area but wanted to ask forum here for perhaps an even shorter/simpler equation or a fix to the below equation.


=IF(C9<>OFFSET(C9,-1,0),
IF(C9=OFFSET(C9,1,0),OFFSET(B9,-1,0)+1&"a",IF(ISNUMBER(OFFSET(B9,-1,0)),
OFFSET(B9,-1,0)+1,LEFT(OFFSET(B9,-1,0),LEN(OFFSET(B9,-1,0))-1)+1)),
LEFT(OFFSET(B9,-1,0),LEN(OFFSET(B9,-1,0))-1)&CHAR(CODE(RIGHT(OFFSET(B9,-1,0),1))+1))