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

nhnn1986

Board Regular
Joined
Oct 12, 2017
Messages
78
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.


ABCDE
1A12345-B9123456-C123454-D20193-00A12345A12346...
2A12345-B9123456-C123456-D20193-00C123456YESYES
3A12345-B9123456-C123455-D20193-00C123455YESNO
4A12345-B9123456-C123456-D20193-00C123444NONO
5A12346-B9123456-C123456-D20193-00
6A12347-B9123456-C123456-D20193-00
7.....
8
9

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,515
Office Version
365
Platform
Windows
Maybe something like this:
Copy formula across and down as needed.
Excel Workbook
ABCD
1A12345-B9123456-C123454-D20193-00A12345A12346
2A12345-B9123456-C123456-D20193-00C123456
YES
YES
3A12345-B9123456-C123455-D20193-00C123455YESNO
4A12345-B9123456-C123456-D20193-00C123444NONO
5A12346-B9123456-C123456-D20193-00
6A12347-B9123456-C123456-D20193-00
Sheet
 

nhnn1986

Board Regular
Joined
Oct 12, 2017
Messages
78
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?
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,515
Office Version
365
Platform
Windows
If your data wouldn't go beyond say row 5000 you could change the above formula to:

Code:
[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
ABCD
1A12345-B9123456-C123454-D20193-00A12345A12346
2A12345-B9123456-C123456-D20193-00C123456
YES
YES
3A12345-B9123456-C123455-D20193-00C123455YESNO
4A12345-B9123456-C123456-D20193-00C123444NONO
5A12346-B9123456-C123456-D20193-00
6A12347-B9123456-C123456-D20193-00
Sheet
 

Watch MrExcel Video

Forum statistics

Threads
1,099,676
Messages
5,470,081
Members
406,680
Latest member
Cleudson Dias de Almeida

This Week's Hot Topics

Top