if first 4 numbers match in sorted list, find last in series

[hmm321]

Hi, column A is a sorted numeric list. IF the first 4 characters match, identify in helper column B which is the last in the series by adding "Last" to the row.
I need to ultimately find root numbers only (1014, 1017, 1020, etc and not the child numbers 1014-1, 1014-2, etc). I figured I could identify the last one and sort again to remove the rest.
Help please?

column A column B
sku last in series
1014-1
1014-2
1014-3
1014-4
1014-5 last
1017-1
1017-2
1017-20
1017-21 last
1020-1
1020-2
1020-3
1020-4
1020-5 last

[Pete_UK]

Try this in B2:

=IF(COUNTIF(A$2:A2,LEFT(A2,4)&"*")=COUNTIF(A:A,LEFT(A2,4)&"*"),"Last","")

then copy down to the bottom of your data.

You will need to fix the values in column B before sorting, as a different sort order will change the values that the formula produces.

Hope this helps.

Pete

[AliGW]

Try this copied down:

=IF(LEFT(A2,4)<>LEFT(A3,4),"Last","")

[hmm321]

Yay! Both work! Appreciate it very much!

[AliGW]

You’re welcome!