Automatically Assign Number to List of Items When List Items Change

[Jpalaci1]

Column A comes from a copied data dump. I need column B to start with the number value one and assign each Value from column A a number when it changes.

For example:

400100 is my first entry and will assign it 1 in column be and will continue to do so until it sees 400200 and then change column B # to 2 and continue until the list finishes. 

When I have numbers they're always 6 characters long. When I have categories they can include % in the string or just text but they always vary in length.

I'm replacing the values in column A at least twice a month (if not more) and being able to assign the values would make it much easier to achieve the specific order it that feeds column A.

I attempted a FIND/SEARCH/MID with nested LEN and couldn't get it to work. I thought about a RANDARRAY or CHOOSE but no success.

Thank you for any help. 

[#2 · Accepted solution]

@Jpalaci1 Added a column to your table with a working formula, although it violates the some of the basics of using structured tables.

[#3]

@Jpalaci1 

These ideas are specific to Excel 365.  365 is so different from legacy spreadsheets that it is sometimes difficult to avoid defaulting to backward compatible solutions and instead embrace the new.  What I have attempted is to build an in-memory lookup table that will map existing IDs to the new sequence number.

= LET(
  ID, UNIQUE([Value]),
  k,  SEQUENCE(COUNTA(ID)),
  XLOOKUP([@Value],  ID, k))

 Just for the hell of it, I also tried to sequence the numeric IDs only

= LET(
  V,  UNIQUE([Value]),
  ID, FILTER(V, ISNUMBER(V)),
  k,  SEQUENCE(COUNTA(ID)),
  XLOOKUP([@Value], ID, k, ""))

[#4]

Thank you for this! Worked liked a charm.

[#5]

Thank you. I used the first answer since it was shorter but tried your solution and it helped just as well. Thank you for showing me new logic in Excel I can use to solve other issues.