Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 22

Thread: Find a multi string match in Column A

  1. #1
    Seeker Shazam's Avatar
    Join Date
    Feb 2019
    Posts
    12
    Articles
    0
    Excel Version
    Microsoft office plus pro 2016

    Find a multi string match in Column A



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello everyone!

    I'm hoping you can help me with this.

    Problem:
    In Column A I have multiple strings, for example:

    1. 20D Fun 0L
    2. 10D Fun 0L
    3. 0L Fun 20D

    As you can see, 1. and 2. have the same information.

    Solution:
    Ideally I would like the formula to search Column A and find cells which have 3 matching strings. In Column B, the result should output the matching cell. For example, in the data I presented, the result for 1. would be 3.

    I appreciate any help.

  2. #2
    Seeker Shazam's Avatar
    Join Date
    Feb 2019
    Posts
    12
    Articles
    0
    Excel Version
    Microsoft office plus pro 2016
    Here's some sample data and how I'd like the results to appear. There only exists 1 duplicate for each cell but the strings are in different order.
    Attached Files Attached Files

  3. #3
    Seeker Shazam's Avatar
    Join Date
    Feb 2019
    Posts
    12
    Articles
    0
    Excel Version
    Microsoft office plus pro 2016
    I found this and it works for what I need, but how do I get it to list through the entire Column A and how would I get it to output the results in column B or at the very least highlight all the matching results in column A?

    Link: https://stackoverflow.com/questions/...order-in-excel
    Last edited by Shazam; 2019-02-27 at 01:09 PM.

  4. #4
    Seeker Shazam's Avatar
    Join Date
    Feb 2019
    Posts
    12
    Articles
    0
    Excel Version
    Microsoft office plus pro 2016

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,568
    Articles
    0
    Excel Version
    365
    Shazam, for your information, you should always provide links to your cross posts.
    https://www.mrexcel.com/forum/excel-questions/1089037-multiple-string-matching.html
    https://www.excelforum.com/excel-formulas-and-functions/1266311-find-a-multi-string-match-in-column-a.html

    This is a requirement, not just a request.
    If you have cross posted at other places, please add links to them too.
    Why? Have a read of http://www.excelguru.ca/content.php?184
    Last edited by p45cal; 2019-03-03 at 02:21 PM.

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,568
    Articles
    0
    Excel Version
    365
    This macro is in the attached and can be run by clicking on Button 1 near cell F2 on the sheet.
    Code:
    Sub blah()
    Set rngToCheck = Range("A2:A35")    'adjust this as required.
    For Each cll In rngToCheck.Cells
      ofset = 1
      cllWords = Split(Application.Trim(cll.Value))
      For Each celle In rngToCheck.Cells
        myCount = 0
        If cll.Address <> celle.Address Then
          celleWords = Split(Application.Trim(celle.Value))
          For Each cllWord In cllWords
            For Each celleWord In celleWords
              If LCase(cllWord) = LCase(celleWord) Then myCount = myCount + 1
              If myCount > 2 Then
                celle.Copy cll.Offset(, ofset)
                ofset = ofset + 1
                FoundOne = True
                Exit For
              End If
            Next celleWord
            If FoundOne Then
              FoundOne = False
              Exit For
            End If
          Next cllWord
        End If
      Next celle
    Next cll
    End Sub
    It looks for 3 matching words (any order). If it finds more than one other matching cell it adds those cell values in more columns to the right.

    Of course, you may need to adjust the range "A2:A35" in the code.
    Attached Files Attached Files
    Last edited by p45cal; 2019-02-28 at 12:50 PM.

  7. #7
    Seeker Shazam's Avatar
    Join Date
    Feb 2019
    Posts
    12
    Articles
    0
    Excel Version
    Microsoft office plus pro 2016
    Quote Originally Posted by p45cal View Post
    Shazam, for your information, you should always provide links to your cross posts.
    https://www.mrexcel.com/forum/excel-...-matching.html
    This is a requirement, not just a request.
    If you have cross posted at other places, please add links to them too.
    Why? Have a read of http://www.excelguru.ca/content.php?184
    Sorry about that, I'll make sure I don't repeat that mistake.

    Here's another link
    https://www.excelforum.com/excel-for...-column-a.html


    Quote Originally Posted by p45cal View Post
    This macro is in the attached and can be run by clicking om Button 1 near cell F2 on the sheet.
    Code:
    Sub blah()
    Set rngToCheck = Range("A2:A35")    'adjust this as required.
    For Each cll In rngToCheck.Cells
      ofset = 1
      cllWords = Split(Application.Trim(cll.Value))
      For Each celle In rngToCheck.Cells
        myCount = 0
        If cll.Address <> celle.Address Then
          celleWords = Split(Application.Trim(celle.Value))
          For Each cllWord In cllWords
            For Each celleWord In celleWords
              If LCase(cllWord) = LCase(celleWord) Then myCount = myCount + 1
              If myCount > 2 Then
                celle.Copy cll.Offset(, ofset)
                ofset = ofset + 1
                FoundOne = True
                Exit For
              End If
            Next celleWord
            If FoundOne Then
              FoundOne = False
              Exit For
            End If
          Next cllWord
        End If
      Next celle
    Next cll
    End Sub
    It looks for 3 matching words (any order). If it finds more than one other matching cell it adds those cell values in more columns to the right.

    Of course, you may need to adjust the range "A2:A35" in the code.
    Thanks for trying out the solution, unfortunately the results are not what I'm looking for. I may not have explained clearly what solution I'm after.

    In the excel file I provided, column B is just an example of how I'd like the results to appear.

    Ex. B2 is the answer/result for A2 because it has 3 matching strings in A17, then a17 was copied over to B2 as the answer. Hope this makes more sense.

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,568
    Articles
    0
    Excel Version
    365
    In what way is my offering not giving the same results?
    Tomorrow, I'll run the macro again, post screenshot of the results and ask you to point out the differences between your column B and mine...

  9. #9
    Seeker Shazam's Avatar
    Join Date
    Feb 2019
    Posts
    12
    Articles
    0
    Excel Version
    Microsoft office plus pro 2016
    Quote Originally Posted by p45cal View Post
    In what way is my offering not giving the same results?
    Tomorrow, I'll run the macro again, post screenshot of the results and ask you to point out the differences between your column B and mine...
    I really appreciate your help. I've attached a screenshot to show the results I'm getting when I click the button.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	excelguru.PNG 
Views:	11 
Size:	48.6 KB 
ID:	8937  

  10. #10
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,568
    Articles
    0
    Excel Version
    365
    Screenshot of macro blah run on your file attached to msg#2 with results in column B.
    Your desired results in column C.
    Apart from the NA entries, what's the difference?
    Click image for larger version. 

Name:	2019-02-28_114415.jpg 
Views:	16 
Size:	74.7 KB 
ID:	8938

Page 1 of 3 1 2 3 LastLast

Posting Permissions

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