In B5 and below try something like
=if(match(A5,A:A,0)=row(),"New",vlookup(A5,A:B,2)+ countif(A:A,A5)-1))

"vijaydsk1970" wrote:

Kindly advise some suggestion for the following:

My data is in 2 columns one column is brand code and other is number which
should be generated by excel with a condition that excel should check up for
the last existing number in sheet and add one number to that existing number.

the following may give better idea.

brand code number

tooth paste 101

brush 201

shaving kit 301

here i will enter in the a5 cell as brush then excel should add 1 to the
existing 201 and show as 202 and tooth paste in a6 then excel should add 1 to
the existing 101
and show as 102

like any other product added in "A" column the next column should be update
with corresponding value

thanks