How can I check a part of string with row value and column value

[nhnn1986]

Hi all
I have workbook with data in sheet(data).range("A1:Ai) , i= last row

Now I want to mark YES with conditions match row and column for each Cell in range(cells(2,3),cells(lr,lc)) , lr = last row in column B, lc = last column in row
Else will mark NO

Data and result like table belove. Please help me.


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A12345-B9123456-C123454-D20193-00[/TD]
[TD][/TD]
[TD]A12345[/TD]
[TD]A12346[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A12345-B9123456-C123456-D20193-00[/TD]
[TD]C123456[/TD]
[TD]YES[/TD]
[TD]YES[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A12345-B9123456-C123455-D20193-00[/TD]
[TD]C123455[/TD]
[TD]YES[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A12345-B9123456-C123456-D20193-00[/TD]
[TD]C123444[/TD]
[TD]NO[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A12346-B9123456-C123456-D20193-00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]A12347-B9123456-C123456-D20193-00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD].....[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Maybe something like this:
Copy formula across and down as needed.

Excel Workbook
	A	B	C	D
1	A12345-B9123456-C123454-D20193-00		A12345	A12346
2	A12345-B9123456-C123456-D20193-00	C123456	YES	YES
3	A12345-B9123456-C123455-D20193-00	C123455	YES	NO
4	A12345-B9123456-C123456-D20193-00	C123444	NO	NO
5	A12346-B9123456-C123456-D20193-00
6	A12347-B9123456-C123456-D20193-00
Sheet

 

[nhnn1986]

Thanks for your fomula but my data is dynamic range so in coumn(A) maybe range(A1:A1000) or range(A1:A500) depend on each month. So Could you have another solution to do this?

 

[nhnn1986]

Some one can help me thanks./.

 

[AhoyNC]

If your data wouldn't go beyond say row 5000 you could change the above formula to:

[TABLE="width: 756"]
<colgroup><col width="756"></colgroup>[TR]
   [TD="width: 756"]IF(SUMPRODUCT(ISNUMBER(SEARCH(C$1,$A$1:[COLOR=#ff0000]$A$5000[/COLOR]))*ISNUMBER(SEARCH($B2,$A$1[COLOR=#ff0000]:$A$5000[/COLOR])))>0,"YES","NO")
[/TD]
 [/TR]
[/TABLE]

Here is a link to set up a dynamic range which you could put in the formula above.
https://www.youtube.com/watch?v=13tY16Y19TY

Or try the following which is an array formula that must be entered with CTRL-SHIFT-ENTER. Then drag formula down and across as needed.

Excel Workbook
	A	B	C	D
1	A12345-B9123456-C123454-D20193-00		A12345	A12346
2	A12345-B9123456-C123456-D20193-00	C123456	YES	YES
3	A12345-B9123456-C123455-D20193-00	C123455	YES	NO
4	A12345-B9123456-C123456-D20193-00	C123444	NO	NO
5	A12346-B9123456-C123456-D20193-00
6	A12347-B9123456-C123456-D20193-00
Sheet