+ Reply to Thread
Results 1 to 9 of 9

Search for a cell string in the same column and indicate match

  1. #1
    Registered User
    Join Date
    10-16-2012
    Location
    Seoul, Korea
    MS-Off Ver
    Excel 2013, Excel 2016
    Posts
    14

    Search for a cell string in the same column and indicate match

    Hi guys,

    This problem sounds really trivial and, thus, I tried to workaround by using excel len, search and count formulas. But since excel can't search for strings longer than 253 characters, I could not get what I am trying for.
    So the problem is:
    I have a workbook with two sheets- "Problem" and "Solution". In the former sheet, there are two columns- one containing IDs and other containing strings. Many of the strings in the column are >253 characters long. I want to compare string of each cell against the whole strings column and if it matches, I want the IDs of the matching strings to be pasted in "Solution" sheet. For Illustration purpose, I am showing the problem and desired solution hereunder:
    Waiting for some assistance !

    Problem Sheet:

    Sets Strings
    Set1 ABCDEFGHIJKLMNOP
    Set2 CDEFGHIJKL
    Set3 ABCDEFGH
    Set4 ABCDEFGHIJKL
    Set5 ABCDFGHIJ
    Set6 JKLMNOP
    Set7 POHIJABCDEF
    Set8 HIJABCDE
    Set9 DMNCDEFGHIJKL
    Set10 RORJKLMNOPSTMNP

    Solution Sheet:

    Sets Match
    Set1 Set1
    Set2 Set1, Set2, Set4, Set9
    Set3 Set1, Set3, Set4
    Set4 Set1, Set4
    Set5 Set5
    Set6 Set1, Set6, Set10
    Set7 Set7
    Set8 Set7, Set8
    Set9 Set9
    Set10 Set10
    Last edited by xprakash; 05-26-2016 at 02:35 AM.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Search for a cell string in the same column and indicate match

    Removed by FR.
    Dave

  3. #3
    Registered User
    Join Date
    10-16-2012
    Location
    Seoul, Korea
    MS-Off Ver
    Excel 2013, Excel 2016
    Posts
    14

    Re: Search for a cell string in the same column and indicate match

    Hi Dave,
    I don't understand why it is "Removed"!
    Would you explain please?

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Search for a cell string in the same column and indicate match

    Yes.

    I thought I had solution. I realized that I missed some of the patterns you were expecting matches on. In particular Set 10 Set 6, Set 10 I could only return Set 10.

    Have been unable to find a way to resolve those that have matches mutually 'buried' in each other.

    I need to spend some time with this. Not as straight forward as I first thought. My apologies.

  5. #5
    Registered User
    Join Date
    10-16-2012
    Location
    Seoul, Korea
    MS-Off Ver
    Excel 2013, Excel 2016
    Posts
    14

    Re: Search for a cell string in the same column and indicate match

    Oh snap!
    I think the error was at my end !

    After reading your reply, I rechecked my illustrated/expected solution and found error which I corrected now.
    Would you look at it again?

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Search for a cell string in the same column and indicate match

    Try array entering this formula in F2 as in example below. Fill down and across until you get blanks in all columns and rows.

    =IFERROR(INDEX($A$2:$A$11,SMALL(IF(ISNUMBER(FIND($B2,$B$2:$B$11)),ROW($A$2:$A$11)-MIN(ROW($A$2:$A$11))+1),COLUMNS($F:F))),"")

    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.



    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    Sets Strings Sets Match
    2
    Set1 ABCDEFGHIJKLMNOP Set1 Set1 Set1
    3
    Set2 CDEFGHIJKL Set2 Set1, Set2, Set4, Set9 Set1 Set2 Set4 Set9
    4
    Set3 ABCDEFGH Set3 Set1, Set3, Set4 Set1 Set3 Set4
    5
    Set4 ABCDEFGHIJKL Set4 Set1, Set4 Set1 Set4
    6
    Set5 ABCDFGHIJ Set5 Set5 Set5
    7
    Set6 JKLMNOP Set6 Set1, Set6, Set10 Set1 Set6 Set10
    8
    Set7 POHIJABCDEF Set7 Set7 Set7
    9
    Set8 HIJABCDE Set8 Set7, Set8 Set7 Set8
    10
    Set9 DMNCDEFGHIJKL Set9 Set9 Set9
    11
    Set10 RORJKLMNOPSTMNP Set10 Set10 Set10

  7. #7
    Registered User
    Join Date
    10-16-2012
    Location
    Seoul, Korea
    MS-Off Ver
    Excel 2013, Excel 2016
    Posts
    14

    Re: Search for a cell string in the same column and indicate match

    Wow!
    Such a neat and short trick!
    I had been banging my head since couple of days tinkering with simple formula and recording macros getting nowhere near solution!


    Thanks mate, you saved days!
    Cheers

    PS. If its okay, would you mind recommending good starter book/s on Excel/VBA?
    Thanks again!!

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Search for a cell string in the same column and indicate match

    xprakash,

    You're welcome, and thanks for the feedback.

    Beyond books by John Walkenbach I wouldn't know what to recommend. I learn most of my stuff here, other sites and just jumping into threads and trying to solve stuff.

    In the Excel Formulas & Functions section there is a post called "Sticky: Excel Useful Links". There are sources listed there including books. It is always the first thread listed. Members speak highly of it.

    Dave

  9. #9
    Registered User
    Join Date
    10-16-2012
    Location
    Seoul, Korea
    MS-Off Ver
    Excel 2013, Excel 2016
    Posts
    14

    Re: Search for a cell string in the same column and indicate match

    Just looked at those listings in suggested section.
    Will try to go through them.

    Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Search column for string and date then search adjacent cell and pop up message
    By ftwobtwo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-06-2014, 09:55 AM
  2. [SOLVED] Formula or Macro, match string,from 1 cell to a column,String use dash
    By david gonzalez in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-07-2013, 06:03 PM
  3. Search for a text string and return adjacent cell value for each match
    By ral8088 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-21-2013, 09:55 AM
  4. [SOLVED] Search Column A and B for string match and return value of column C
    By RichTea88 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-18-2013, 10:02 AM
  5. [SOLVED] Search for String in Range, If Match, Return Matching Cell Value
    By ryanb909 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-21-2013, 02:27 AM
  6. [SOLVED] Vlookup, Match (Search or Find) partial string within string in a Cell
    By dluhut in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-28-2013, 12:40 PM
  7. [SOLVED] Search for string across header row, then search for another string down found column
    By TucsonJack in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-14-2012, 02:09 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1