# Thread: Find a multi string match in Column A

1. ## Find a multi string match in Column A

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. 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.

3. 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?

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.
Why? Have a read of http://www.excelguru.ca/content.php?184

5. 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
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.

6. Originally Posted by p45cal
https://www.mrexcel.com/forum/excel-...-matching.html
This is a requirement, not just a request.
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.

https://www.excelforum.com/excel-for...-column-a.html

Originally Posted by p45cal
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
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.

7. 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...

8. Originally Posted by p45cal
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.

9. 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?

Page 1 of 3 1 2 3 Last

#### Posting Permissions

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