I need to input the status in file-baseline.xlsx when there is a partial match in whitelisted software.xlsx based on a column, not the other way.
That is, filter whitelisted software.xlsx (columnA) for file-baseline (columnB). However, whitelisted software.xlsx which is the lookup file has values that are shorter than file-baseline. As such I am not even sure if filter wizard is appropriate.
For easy appreciation, eg.
In file-baseline.xlsx ‘ClearPass OnGuard 18.104.22.168195’ when searched in whitelisted software.xlsx should be considered a match even when whitelisted software.xlsx has value ‘ClearPass OnGuard’.
Appreciate if any expert out there could provide the sample code for all to learn. Thanks.
First of all, your whitelist should be cleaned (eliminate versions and comments about duration).
Then take a look at this and tell me if it’s what you want
C: column that contains text that I have “guessed” from the whitelist
D: number of characters on which I have made the match
E: TRUE if the guess was good, FALSE otherwise
Take the first Nb chars from Software, add a * as wildchar and search it in the whitelist, doing a non exact match
Check if the guessed match matches the searched string, limited on the length of the entry in whitelist
macro in file-baseline.xlsb
Dim i As Integer
Dim Start As Date
Dim Interval As Date
Start = Now
For i = 2 To 7000
Range("D" & i).Value = 1
While Not IsError(Range("C" & i).Value) And Range("D" & i).Value <= Len(Range("B" & i).Value)
Range("D" & i).Value = Range("D" & i).Value + 1
Range("D" & i).Value = Range("D" & i).Value - 1
Interval = Now - Start
MsgBox "Done in " & Int(CSng(Interval * 24 * 60)) & ":" & Format(Interval, "ss")
Set Column D to 1 and add 1 until we get #N/A error (no string matched), then decrease one to come back to the last string matched (7000 needs to match your last line)
This macro can be modified and injected from UiPath, if you have additional processing to be done.
The codes look complex. Any chance of it being done in UIPath as we do not understand macro. We also want to automate it as the the input files are dynamic and subject to change. Let us know your thoughts.