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

 

[hagia_sofia]

Try this:

=BYROW(B1:B4,LAMBDA(a,IF(SUM(--(LEFT(a,14)=LEFT($B$1:$B$4,14)))>1,"YES","NO")))

 

[AhoyNC]

Another option.

Book1
	A	B
1	YES	XOM14287469874
2	YES	XOM14287469874M
3	NO	XBO15478715455
4	NO	XBO15478715451X
5	YES	XOM14287469874Z5
6	NO	XOM142874984Z
7	YES	XBO15478715456
8	YES	XBO15478715456RT
Sheet1

Cell Formulas
Range	Formula
A1:A8	A1	=IF(ROWS(FILTER($B$1:$B$8,LEFT(B1,14)=LEFT($B$1:$B$8,14)))>1,"YES","NO")

 

[Fluff]

Another option

Fluff.xlsm
	A	B
1	Yes	XOM14287469874
2	Yes	XOM14287469874M
3	No	XBO15478715455
4	No	XBO15478715451X
Data

Cell Formulas
Range	Formula
A1:A4	A1	=MAP(LEFT(B1:B4,14),LAMBDA(m,IF(COUNTIFS(B:B,m&"*")>1,"Yes","No")))
Dynamic array formulas.

 

[Tetra201]

Yet another one (assuming all serial numbers in column B are at least 14-symbol long):

=IF(COUNTIFS(B:B,LEFT(B1:B4,14)&"*")>1,"YES","NO")

 

[Utradeshow]

I may have not stated the example properly, All serials are in Column B and fluctuates, currently there are 40233 rows. so I need the formula to check ALL of column B.

 

[Fluff]

All the formulae suggested will do that. Just change the ranges to suit.

 

[Utradeshow]

All the formulae suggested will do that. Just change the ranges to suit.

Excellect, Thank you so Much! I will test them out and see what happens

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Utradeshow]

Is there a way to keep the formula LIGHT on resources?
The column is sorted Ascending, so the formula in A2 would only have to compare B2 and B3? These formulas copied down column A is crashing my computer.