A incremental Auto numbering based on two columns data criteria

I would like to ask how to write a formula or macro as below needs:

My column "A" contains Codes and column “B” contains a list of categories, which has different codes for each category. And for column C "Auto number Result", I would like to get a continuous number for each category from starting 1. Please see below my requirement

• The first number for each categary should be start with "1" then increasing as per code in that category like 1,2,4,5
• when code in category changes, the number should start over 1 in column c and go on further
• It means as per category and its code type I want no like 1,2,34……
• When I entered a new row with a new code with a existed category then its sequential number should be for e.g. if in category “sp5” last auto number is 4 and if new code in same category “sp5” then next incremental number would be 5
• If same code in written for a category then auto number would be same.
• also if I add or removed any row then the numbers wonts get changed, which already written

I m not expert in excel but somehow i have managed to search a array (ctrl+Shift+Enter) formula and i got it but whenever I changes in data the auto number gets change, because its actually doing ranking, but i not need to rank it, i just want a unique number.

Formula:

Please Login or Register  to view this content.

Attached my sample file and what result i expected. also given below image.

Attachment 566503

Need a formula or VBA code, formula will be preferable !
Really appreciate for Help !

I hope you can help me with this problem I have. I can't seem to find a solution to this.

Looking for help !!

Hello

Guys Pls help me !

Is it that much difficult or is it impossible ??

Thanks John for replying me !

Sorry I hv not seen your reply and before saying our reply I quoted. Yes I just saw your reply and tried in my file, its working only the thing I wanna tell you, i will illustrate to u using below images

1)
1.JPG

in this image in cell A24 code is 2209 and its number is 5 and in cell A26 code is 3204 its number is 6

2)
2.JPG

now i have changed the A24 code to 4565 and its number get changed to 6 but number in C 26 gets changed to 7

3)
3.JPG

what I want is though I changed the value in A24 then also C26 doesnt get changed it should be same as before means 6 and new number for C24 should be next number to C26 that is 7

Hope you understand my query

Can you please provide me VBA code

Thanks for helping me

Where and how can I ask moderator to transfer it, And i explained it what i want

I have (hopefully) contacted a moderator and requested the transfer.




Edit by DominicB : Moved to VBA sub-forum.

Thank you for helping

Please provide me VBA code

My list will go on so I need vba code subsequent run where new data will be added regularly

How will we know if you have changed an existing code: [or Category (?)] need to use Worksheet_Change code.

If NEW codes added then these will be identified by column C being blank (?).

Starting to get complicated!!!

Guys , any progress