Identifying duplicates

[12Rev79]

Dear Expert,

I have the following in
column A Column B
15345 L1-FAPP-B1:P14
15346 L1-FAPP-B1:P15
15347 L1-FAPP-B1:P16
15348 L1-FAPP-B1:P17

since L1-FAPP-B1 is common I need this result in column C

column A Column B column C
15345 L1-FAPP-B1:P14 -
15346 L1-FAPP-B1:P15 Duplicated to Item # 15345
15347 L1-FAPP-B1:P16 Duplicated to Item # 15345
15348 L1-FAPP-B1:P17 Duplicated to Item # 15345

Please can you help me figure out the sytax.

Thank you expert in advance.

Regards,

12Rev79

 

[Kaper]

This in C2 shall work in 365 version. In 2010 instead of textsplit find a position of colon in a text and use LEFT function.

=IFERROR("Duplicated to item # " & INDEX(A$1:A1,MATCH(INDEX(TEXTSPLIT(B2,":"),1,1)&"*",B$1:B1,0)),"-")

Book1
	A	B	C
1
2	15345	L1-FAPP-B1:P14	-
3	15346	L1-FAPP-B1:P15	Duplicated to item # 15345
4	15347	L1-FAPP-B1:P16	Duplicated to item # 15345
5	15348	L1-FAPP-B1:P17	Duplicated to item # 15345
Sheet3

Cell Formulas
Range	Formula
C2:C5	C2	=IFERROR("Duplicated to item # " & INDEX(A$1:A1,MATCH(INDEX(TEXTSPLIT(B2,":"),1,1)&"*",B$1:B1,0)),"-")

 

[12Rev79]

This in C2 shall work in 365 version. In 2010 instead of textsplit find a position of colon in a text and use LEFT function.

=IFERROR("Duplicated to item # " & INDEX(A$1:A1,MATCH(INDEX(TEXTSPLIT(B2,":"),1,1)&"*",B$1:B1,0)),"-")

Book1
	A	B	C
1
2	15345	L1-FAPP-B1:P14	-
3	15346	L1-FAPP-B1:P15	Duplicated to item # 15345
4	15347	L1-FAPP-B1:P16	Duplicated to item # 15345
5	15348	L1-FAPP-B1:P17	Duplicated to item # 15345
Sheet3

Cell Formulas
Range	Formula
C2:C5	C2	=IFERROR("Duplicated to item # " & INDEX(A$1:A1,MATCH(INDEX(TEXTSPLIT(B2,":"),1,1)&"*",B$1:B1,0)),"-")

Thanks Kaper, it works well.....appreciated.

 

[Kaper]

Glad to hear it works for you!

 

[Peter_SSs]

Thanks Kaper, it works well.....appreciated.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.