Lookup values from a range with partial non-ordered keyword strings

[ainotayev]

Hello, all!

I'd appreciate help in solving my problem of looking up values by "Search String - Out of order" from the array in the Taxonomy Sheet into the Main sheet's Taxonomy column. The lookup has to match by all of the words in the Search String, although they're not necessarily in matching order. In some cases, the full Description could contain a "-" in "Q-Deck Sleeve" whereas the Search String term doesn't. Please reference the attached file. The solution could be an excel formula or a macro.

Thanks in advance!

[p45cal]

In the attached, a work-in-progress function called Taxonomy. I foresee a problem where you want Q only to be found as a separate character but AUV to be found within a word. At the moment, if more than one taxonomy is found, all are included separated by a /.

[ainotayev]

Thank you, kind sir! I will need to consolidate the array further as I work through the process, for sure!

[ainotayev]

This might be helpful - I used the following code originally to identify the search terms from the existing taxonomy and it works beaufitully;

30391


Option Compare Text
Private Sub CompareWords()
Dim xStr() As String
Dim i As Long
Dim x As Long, y As Long
With ActiveSheet
For i = 2 To .Cells(.Rows.Count, "G").End(xlUp).Row
xStr = Split(.Cells(i, "G").Value, " ")
With .Cells(i, "H")
.Font.ColorIndex = 1
For x = LBound(xStr()) To UBound(xStr())
For y = 1 To Len(.Text)
If Mid(.Text, y, Len(xStr(x))) = xStr(x) Then
.Characters(y, Len(xStr(x))).Font.ColorIndex = 3
End If
Next y
Next x
End With
Next i
End With
End Sub

Function GetColorText(pRange As Range) As String
Dim xOut As String
Dim xValue As String
Dim i As Long
Dim wasRed As Boolean
xValue = pRange.Text
For i = 1 To VBA.Len(xValue)
If pRange.Characters(i, 1).Font.Color = vbRed Then
xOut = xOut & VBA.Mid(xValue, i, 1)
wasRed = True
ElseIf wasRed = True Then
wasRed = False
xOut = xOut & " "
End If
Next
GetColorText = xOut
End Function

Function RemoveDupes2(txt As String, Optional delim As String = " ") As String
Dim x
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each x In Split(txt, delim)
If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
Next
If .Count > 0 Then RemoveDupes2 = Join(.keys, delim)
End With
End Function

[ainotayev]

Even my I7-12700KF is struggling with this one!

I added "/" to the replace. function and that helped to identify even more matches.

[ainotayev]

Hello. I'd appreciate help with converting the following Macro to a Function so I can select the value column/cell and the array to lookup from like any other formula, please. Instead of highlighting the found text in red, I'd like to lookup the associated values from the array like vlookup, if possible.


Private Sub CompareWords()
Dim xStr() As String
Dim i As Long
Dim x As Long, y As Long
With ActiveSheet
For i = 2 To .Cells(.Rows.Count, "G").End(xlUp).Row
xStr = Split(.Cells(i, "G").Value, " ")
With .Cells(i, "H")
.Font.ColorIndex = 1
For x = LBound(xStr()) To UBound(xStr())
For y = 1 To Len(.Text)
If Mid(.Text, y, Len(xStr(x))) = xStr(x) Then
.Characters(y, Len(xStr(x))).Font.ColorIndex = 3
End If
Next y
Next x
End With
Next i
End With
End Sub

[Paul_Hossler]

1. I added CODE tags for you

2. Functions cannot change things like WS formatting

3. I think this in the area of what you were asking


30416




Option Explicit

Function MyLookup(v As Variant, r As Range, n As Long) As Variant
MyLookup = Application.VLookup(v, r, n, False)
End Function

[ainotayev]

Hey, Paul.

Thank you for your effort, but I'm not looking for a straight vlookup. Instead, I'd like to utilize the existing code which finds like text in a string from an array and coloring the found text in red using the line ".Characters(y, Len(xStr(x))).Font.ColorIndex = 3", I'd like it to lookup a value from the array.

[Paul_Hossler]

Hello. I'd appreciate help with converting the following Macro to a Function so I can select the value column/cell and the array to lookup from like any other formula, please. Instead of highlighting the found text in red, I'd like to lookup the associated values from the array like vlookup, if possible.






2. Functions cannot change things like WS formatting


Sorry I misunderstood

Maybe you should post an example

[SamT]

This assumes that (i, "H") is a part of a longer word in xStr(x). Finds "Star" in "LoneStarState", (but not in "LonestarState".)

For y = 1 To Len(.Text)
If Mid(.Text, y, Len(xStr(x))) = xStr(x) Then

If I understand your code correctly, it is basically
For i = Blah blah
If Instr(Cells(i, "G"), Cells(i, "H")) Then part_of_Cells(i, "G"). Font = Red

In your post you want a Function with one Parameter that is one ValueCell to return an array of values. What Array of which Values?

It would help us help you if you would share a short example of a "Value Column," a "Value Cell," and what the Function Return looks like.

It would also help if you gave a verbal description of what you need, rather than a confused bit of code.

[p45cal]

It's not at all clear what you want. Share a workbook with realistic values and manually show us the expected results of this function. If the 2 ranges are not going to be of equal size, show examples of expected results when they are not equal.

Anything like this?:

30418

[ainotayev]

A happy and prosperous new year to you all, fellas!

I ask that you please reference my previous thread (http://www.vbaexpress.com/forum/showthread.php?70477-Lookup-values-from-a-range-with-partial-non-ordered-keyword-strings) on the same subject for details and sample data. (See Next post: by Mod. ST.) The problem I have with the solution in that thread is that the provided code is extremely processor intensive and doesn't seem to do as well in finding matches as the code posted in the first post.

Thanks!

[georgiboy]

Are you after something like the attached?

[ainotayev]

This is great, thank you!

Is it possible to make mVar and TaxVar dynamic ranges? The number of rows in both are constantly changes. Better yet, can the mVar be user selectable, maybe a range of cells within the column?


Are you after something like the attached?

[georgiboy]

Attachment wih those ranges dynamic.

To make a selectable range you could use something like the below

mVar = Application.InputBox(Title:="Select Range", Prompt:="Select Range", Type:=8).Value

[ainotayev]

Perfect, thank you!


Attachment wih those ranges dynamic.

To make a selectable range you could use something like the below

mVar = Application.InputBox(Title:="Select Range", Prompt:="Select Range", Type:=8).Value