Search code examples
excelvbaexcel-formulakeyexcel-2010

Excel - key generator

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.

Solution 
  • =CONCATENATE(LEFT(C3,2),COUNTIF($C$2:C3,C3))

    The above formula in A3 cell will look into C3 for the first two letters and then counts up to the next number for the same two letters. So if C3 is Parts and Pa2 is already there then A3 will result as Pa3.

    I am done. Thanks all you helped but I resolved it my way.