Search content in another cell as word, not sentence.

[tokyoboy]

Hi,

I am working on formula to help my staff on their data entry.

We have the primary code in column A and we need to input color of each item in column B
In column C, we then have this formula, =IF(A2<>A1,B2,IF(ISNUMBER(SEARCH(B2,C1)),C1,C1&","&B2))

it basically check if all these 3 input is in the same group by checking if they have the same primary key. If yes, then it will check if the cell above (in column C) already have the same data as the one in the same row in column B. So at the last line, the formula see that the cell above do not yet have "grey" which is the in column B, then the formula combine this into the cell and become "Dark Black, Grey"

However, I actually need the result at the last line like this > "Dark Black, Black, Grey". I realize it actually because the "black" is in the sentence "Dark black" that why I could get them into the "Dark Black, Black, Grey"

Is there anyway I can make formula to work so that Excel can include "Black" in row2 into the sentence, even-though we have this "Black" word in "Dark Black" ?


[TABLE="class: grid, width: 705"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Dark Black[/TD]
[TD="align: center"]Dark Black[/TD]
[TD="align: center"]Dark Black,Grey[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Black[/TD]
[TD="align: center"]Dark Black[/TD]
[TD="align: center"]Dark Black,Grey[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Grey[/TD]
[TD="align: center"]Dark Black,Grey[/TD]
[TD="align: center"]Dark Black,Grey[/TD]
[/TR]
</tbody>[/TABLE]


Thank you very much

 

[pgc01]

Hi
Welcome to the board

Include the separator in the search

Instead of

SEARCH(B2,C1)

try

SEARCH(","&B2&",",","&C1&",")

 

[tokyoboy]

HI pgc01,

Thank you very much ! It works !
Have been trying several days to look for solution but really couldn't

Thank for your help pgc01 !

Best Regards,

 

[pgc01]

You're welcome.