Autofill one column based on another column using a certain logic

Autofill one column based on another column using a certain logic
Hi,

I have two columns in my excel sheet say EventName and EventId ,where based on the EventName the Event_id should get auto-populated.

Say if the value of event_name is ab the event_id would be 1 and if it's xy the event-id would be 2,but again if the event_name is ab,the event_id should be 1.

I am attaching here a example table of how the entries should look like:

Event_id Event_Name
1 ab
2 bc
3 xy
1 ab
3 xy
4 df
5 ed
2 bc

Hence when the Event-name is occurring for the first time in Event_Name column the event_id column should get populated with the max value+1 for that column,while for same entry in Event_name column the event_id should get the corresponding value of the same event_name.

Can someone please guide me on how to achieve this?

Thanks
Arnab

Hi Moderator,

Thank-you very much,it really worked.

I should have mentioned that I needed it in macro or formula.But luckily this time I needed it in Formula only.

Thanks Arnab