Combine 2 Cell value with "/" and to exclude repeated values

[Ranjith M]

Dear Experts ,

I need a Help in Excel that the query i have is to Combine 2 Cell value with "/" and to exclude repeated values

example :

cell A1 : I have a Pen to write
Cell B1 : have a Pencil to write
Cell C1 : i have a book to read

output i expected is

Cell D1 : i have a Pen/Pencil to write
Cell E1 or F1 : i have a Book to read

Please help to find a way to combine and to remove the similar words.

 

[amit_gupta123]

Can you share a sample worksheet to understand the requirement in better manner

 

[Ranjith M]

example mentioned in the excel attachment

 

[Ranjith M]

Dear experts , can some one please help with the above one?

 

[bosco_yip]

Try,

1] In Output 1 F2, formula copied down:

=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",99)),99*(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)))&"/"&IF(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))=TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)),TRIM(LEFT(RIGHT(SUBSTITUTE(B2," ",REPT(" ",30)),90),30)),TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",100)),300)))&" and "&TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),299))

2] In Output 1 I2, formula copied down:

=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",99)),99*(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)))&"/"&IF(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))=TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)),TRIM(LEFT(RIGHT(SUBSTITUTE(B2," ",REPT(" ",30)),90),30)),TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",100)),300)))&" and "&C2

 

[Ranjith M]

This is awesome , let me work out bro so far output comes like charm.