search for rows in table that contains all words from a string
Hy everyone,
after searching and trying a lot with formulas and VBA Examples I had no luck to find a solution for my problem. So I ask you for your help.
I have the following problem:
I get a lot of strings with product names and I have to find the matching ID for these strings. The problem is, that these strings to not match with my tables that contains the product names because of a different order of the words in these strings. So I had the following idea:
A macro "splits" the string into single parts and look in the tables, if a row contains all of these parts. And, when found, show the ID.
In my workbook I have examples to show my idea.
I hope you can help me.
Re: search for rows in table that contains all words from a string
One quick question. In cell C4 I can see the result. Very cool. But in cells C9 and C 15 are results too. These cells were just for the examples. How can the code be modified that C2 is the only cell to search and cell C4 is only for the results? Thank YOU
Re: search for rows in table that contains all words from a string
Hi Mick,
thanks for your help. After thinking about your code I had a great idea.
My first idea was to find one position and then the next. But it would be awesome, if I can search for more results in one step.
So I only have to type in the search text for n positions and then I get the result for each of them.
For each search there is only one result. Everything in the tables is unique. (I hope so :-) )
Sorry for take your time with all my questions.
According to your post #7 attachment try this demonstration :
PHP Code:
Sub Demo() Dim Rg As Range, Rf As Range, SP, V, W&, A$, N&, R&, C& For Each Rg In Tabelle1.UsedRange.Offset(1).Columns(3).SpecialCells(xlCellTypeConstants) SP = Split(Rg.Value) V = "Nothing !" For W = 2 To ThisWorkbook.Worksheets.Count With ThisWorkbook.Worksheets(W).UsedRange Set Rf = .Find(SP(0), , xlValues, xlWhole, xlByRows) If Not Rf Is Nothing Then A = Rf.Address Do N = 0 R = Rf.Row - .Row + 1 If Application.CountA(.Rows(R)) - 2 = UBound(SP) Then For C = 1 To UBound(SP) If .Rows(R).Find(SP(C)) Is Nothing Then Exit For N = N + 1 Next If N = UBound(SP) Then V = .Cells(R, 1 - (.Column = 1)).Value: Exit For End If Set Rf = .Find(SP(0), Rf) Loop Until Rf.Address = A End If End With Next Rg(1, 2).Value = V Next Set Rf = Nothing End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 04-17-2017 at 08:33 PM.
Reason: optimization …
Re: search for rows in table that contains all words from a string
Hi, it´s me again
with your help I am very happy and it really works great. But I have another question and I hope you can help me.
The macro from MickG is the one I am using. It founds more results than the macro from Marc L ( I don´t know why).
I tried to modify the macro that this would work:
If the row with the matching strings is found, then the value from row x, column B is written in Sheet ("input") column D. Very cool.
Now I tried with vlookup and VBA to get the values from C:H from the sheet and row where the founded value is from and put it into sheet ("input") column E.
So I get the "original" name. But there are problems with finding this matching cell and there are a lot of spaces when cells are empty in my tables.
The macro from MickG is the one I am using. It founds more results than the macro from Marc L ( I don´t know why).
As my code works only for exact match of words number !
Example with "green car", my code does not return 100,27 from Data_01!B4 ('cause of "red green car" …)
neither 100,30 from B7 (aka "big green House 4 car") but 200,16 from Data_02!B4 as "car green" …
A new demonstration updating column E :
PHP Code:
Sub Demo2() Dim Rg As Range, Rf As Range, SP, V, W&, A$, N&, R&, C& For Each Rg In Tabelle1.UsedRange.Offset(1).Columns(3).SpecialCells(xlCellTypeConstants) SP = Split(Rg.Value) V = [{"Nothing !",""}] For W = 2 To ThisWorkbook.Worksheets.Count With ThisWorkbook.Worksheets(W).UsedRange Set Rf = .Find(SP(0), , xlValues, xlWhole, xlByRows) If Not Rf Is Nothing Then A = Rf.Address Do N = 0 R = Rf.Row - .Row + 1 If Application.CountA(.Rows(R)) - 2 = UBound(SP) Then For C = 1 To UBound(SP) If .Rows(R).Find(SP(C)) Is Nothing Then Exit For N = N + 1 Next If N = UBound(SP) Then V = Array(.Cells(R, 1 - (.Column = 1)).Value, _ Application.Trim(Join(Application.Index(.Rows(R).Offset(, 1).Value, , 0)))) Exit For End If End If Set Rf = .Find(SP(0), Rf) Loop Until Rf.Address = A End If End With Next Rg(1, 2).Resize(, 2).Value = V Next Set Rf = Nothing End Sub
You may like it !
Last edited by Marc L; 04-19-2017 at 05:24 AM.
Reason: typo …
Re: search for rows in table that contains all words from a string
Hi Marc L
thanks for your answer. Your macro shows a lot of errors in the cells in column E and D.
I need a macro (MickG) that searches if the words are in a row in a table. Because the strings I get are not always in the order I want to.
e.g. I would need: this green car
and I get green car this
Re: search for rows in table that contains all words from a string
Hy Marc L,
I have tested it and it looks good. It finds the right results. I don´t know what happened before.
I have 2 more questions. I wrote them into the workbook, because it is a little bit difficult to explain without a workbook.
And because of my english :-)
Sub Demo3() Dim Rg As Range, Rf As Range, SP, V, U%, W&, A$, N&, R&, C& For Each Rg In Tabelle1.UsedRange.Offset(1).Columns(3).SpecialCells(xlCellTypeConstants) SP = Split(Rg.Value) V = [{"Nothing !",""}] U = 0 Do For W = 2 To ThisWorkbook.Worksheets.Count With ThisWorkbook.Worksheets(W).UsedRange Set Rf = .Find(SP(0), , xlValues, xlWhole, xlByRows) If Not Rf Is Nothing Then A = Rf.Address Do N = 0 R = Rf.Row - .Row + 1 If Application.CountA(.Rows(R)) - 2 = UBound(SP) Then For C = 1 To UBound(SP) If .Rows(R).Find(SP(C)) Is Nothing Then Exit For N = N + 1 Next If N = UBound(SP) Then V = Array(.Cells(R, 1 - (.Column = 1)).Value, _ Application.Trim(Join(Application.Index(.Rows(R).Offset(, 1).Value, , 0)))) Exit For End If End If Set Rf = .Find(SP(0), Rf) Loop Until Rf.Address = A End If End With Next If LBound(V) = 0 Or (UBound(SP) < 2 And U = 0) Or U > UBound(SP) + 1 Then Exit Do SP = Split(Rg.Value) SP = Filter(SP, SP(U), False): If UBound(SP) < 0 Then Exit Do U = U + 1 Loop Rg(1, 2).Resize(, 2).Value = V Next Set Rf = Nothing End Sub
You should - click - like it ‼
Later when I'll have time I'll think for a better combinations that your incomplete way …
Sub Demo4() Dim Rc As Range, Rg As Range, SP, V, W&, N&, S With Application .ScreenUpdating = False For Each Rc In Tabelle1.UsedRange.Offset(1).Columns(3).SpecialCells(xlCellTypeConstants) SP = Split(Rc.Value) V = Array("Nothing !", "", -1) For W = 2 To ThisWorkbook.Worksheets.Count For Each Rg In ThisWorkbook.Worksheets(W).UsedRange.Rows N = -1 For Each S In SP If Not Rg.Find(S, , xlValues, xlWhole) Is Nothing Then N = N + 1 Next If N = UBound(SP) And .CountA(Rg) - 2 = UBound(SP) Then V = Array(Rg.Cells(1).Value, .Trim(Join(.Index(Rg.Offset(, 1).Value, , 0)))) Exit For ElseIf N > V(2) Then V = Array(Rg.Cells(1).Value, .Trim(Join(.Index(Rg.Offset(, 1).Value, , 0))), N) End If Next If UBound(V) = 1 Then Exit For Next Rc(1, 2).Resize(, 2).Value = V Next Set Rg = Nothing .ScreenUpdating = True End With End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 04-19-2017 at 08:19 PM.
Reason: optimization …
Re: search for rows in table that contains all words from a string
Hy, it´s me again :-)
I tested it in the last days and it works good. But there is one thing I cannot handle.
I need help when I want to search manually besauce it is possible that there are some articles with the same name and attributes on different sheets.
In my workbook I have an example how it should work.
LinkBack URL
About LinkBacks
Register To Reply
Another way …
Originally Posted by moosmahna
Try this !
Bookmarks