Results 1 to 5 of 5

Thread: How can I check a part of string with row value and column value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2017
    Posts
    66
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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.


    A B C D E
    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
    7 .....
    8
    9

  2. #2
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,168
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

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

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

    ABCD
    1A12345-B9123456-C123454-D20193-00 A12345A12346
    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

    Spreadsheet Formulas
    CellFormula
    C2=IF(SUMPRODUCT(ISNUMBER(SEARCH(C$1,$A$1:$A$6))*ISNUMBER(SEARCH($B2,$A$1:$A$6)))>0,"YES","NO")


    Excel tables to the web >> Excel Jeanie HTML 4

  3. #3
    Board Regular
    Join Date
    Oct 2017
    Posts
    66
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  4. #4
    Board Regular
    Join Date
    Oct 2017
    Posts
    66
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Some one can help me thanks./.

  5. #5
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,168
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

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

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

    Code:
    IF(SUMPRODUCT(ISNUMBER(SEARCH(C$1,$A$1:$A$5000))*ISNUMBER(SEARCH($B2,$A$1:$A$5000)))>0,"YES","NO")
    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.

    ABCD
    1A12345-B9123456-C123454-D20193-00 A12345A12346
    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

    Spreadsheet Formulas
    CellFormula
    C2{=IF(SUMPRODUCT(ISNUMBER(SEARCH(C$1,$A$1:INDEX(A:A,MAX(ROW($A:$A)*($A:$A<>"")))))*ISNUMBER(SEARCH($B2,$A$1:INDEX(A:A,MAX(ROW($A:$A)*($A:$A<>""))))))>0,"YES","NO")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •