Excel file1 lookup file2 where file2 has shorter values

Help
1

Hi,

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

file-baseline.xlsx (105.7 KB)

whitelisted software.xlsx (16.7 KB)

2

Hi,

First of all, your whitelist should be cleaned (eliminate versions and comments about duration).

image

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

image
image1190×461 30.2 KB

3

Hi @c.ciprian

I am impressed and in awe. So it’s possible. Please let us know. Thx!

4

All is done via macros and formulas in Excel, no UiPath involved.

Column C

VLOOKUP(LEFT([@Software],[@Nb])&"*",'whitelisted software.xlsx'!Table1[Software Name],1,FALSE)

Take the first Nb chars from Software, add a * as wildchar and search it in the whitelist, doing a non exact match

Column E

=LEFT([@Software],LEN([@Status]))=[@Status]

Check if the guessed match matches the searched string, limited on the length of the entry in whitelist

macro in file-baseline.xlsb 

Sub matching()
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
    Wend
    Range("D" & i).Value = Range("D" & i).Value - 1
Next i

Interval = Now - Start

MsgBox "Done in " & Int(CSng(Interval * 24 * 60)) & ":" & Format(Interval, "ss")

End Sub

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.

solution.zip (188.7 KB)

1 Like
5

Thanks @c.ciprian

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.

6

The code is very simple. To do it with UiPath I would inject the macro and run it.
To do it only with UiPath activities would be very tedious.

UiPath it’s a great tool, but you need to use the best tool for the job (i.e. you can work with uip in linux console to do stuff, but there are other tools better suited for this case)

1 Like
7

I see. IN this case, just to get the program working in UIPath, is it possible for u to create a UIPath program and inject it. Then we try to undertands the macro bit by bit.

8

The macro is very simple. For UiPath you need to use Invoke VBA activity in order to use the macro, but first of all you need the formulas.
Check this post as it relates to both your needs

2 Likes
9

Thanks @c.ciprian