vba - Excel - key generator

Question

I have a worksheet with types (B2) and ID(A2). The types are Big, Small and Medium. I am using below formula to generate ID from the type name. So for B2 value Small the A2 value will Sm1. Below formula is written on A2.

=CONCATENATE(LEFT(B2,2),COUNTIF($B$2:B2,B2))

The issue is, I want the formula (or add vba code) to look up column A and if the B column value is Medium then it must see the last number with Me in the column A and put next number.

question from:https://stackoverflow.com/questions/66050620/excel-key-generator

Answer 1

The easiest way to this is to add a number column via

=COUNTIFS(B$1:B2,"="&B2)

Then just combine the B column with the value from this new column

This is easier than trying to find the last value before this one, but gives the same result.