Match LEFT Characters in column

[Utradeshow]

I have a column that contain serial Numbers. I need to match the ones that the LEFT 14 characters match.

Asset Status Report 06.20.24.xlsx
	A	B
1	YES	XOM14287469874
2	YES	XOM14287469874M
3	NO	XBO15478715455
4	NO	XBO15478715451X
Sheet1

 

[Fluff]

The formulae in posts 2 & 5 do not need to be copied down. Just put one in A1 & it will spill down.

 

[Joe4]

If you only ever need to look at the row above and below because your data is sorted, then perhaps a formula like this for cell A2 (assuming your have a header row in row 1):

=IF(OR(LEFT(B2,14)=LEFT(B1,14),LEFT(B2,14)=LEFT(B3,14)),"YES","NO")

and copy down.

 

[Utradeshow]

If you only ever need to look at the row above and below because your data is sorted, then perhaps a formula like this for cell A2 (assuming your have a header row in row 1):

=IF(OR(LEFT(B2,14)=LEFT(B1,14),LEFT(B2,14)=LEFT(B3,14)),"YES","NO")

and copy down.

Actually, this one works great I think. It is also LIGHT on the resources.