Searching a value from one column in excel to another column in other excel

excel
activities

#1

Hi,

Currently as a part of my task i am suppose to search a name from one excel1 to a name in another excel2.
The search criteria is

1.Firstly i will search for the full name in excel1 to find whether the name matches in excel 2
2.If a match is not found then every time i will remove the last word from name in excel 1 and then search again for the name in excel 2
3.Here in this searching method i have used the like keyword from VB Dot net but as per the results the number of matches are many which is not expected.We are only expecting a 5-10 percent match using name searching criteria

The client is interested in applying some kind of fuzzy logic to search for the name so that the number of matches are reduced .

Is there any other more precise way of searching in VB dot net. I know that we can use exact search technique but it will not work .
Can you guys suggest some new technique ?
Can we use contains function?


#2

Hello,

Since you need to edit excel1, I believe you have 2 options for that… 1. use a ForEach (maintainable but slower) or 2. convert the table to a string and use .Select to concatenate it together with the change (faster but more complicated)

So let’s take the ForEach and here are the steps that I would take:

  1. ForEach row In excel1
  2. Filter excel2 to match
  3. edit row to remove last word

So it would look something like this:

ForEach row In excel1

matchCount = excel2.AsEnumerable.Where(Function(row2) row2(“column2”).ToString.Contains(row.Item(“column1”).ToString.Trim)).ToArray.Count

If matchCount=0 Then
row(“column1”) = String.Join(" “,row(“column1”).ToString.Trim.Split(” “c).Take(row(“column1”).ToString.Trim.Split(” "c).Count-1))

where “column1” is the column in excel1 with the full name and “column2” is the column in excel2 with the name. You might need additional logic if you are not sure of column names. You will also need more logic if the full name can include words like a middle name in one excel and not in the other.

Hope this helps or strikes some ideas.

Thanks.

Clayton.


#3

No, the client is intertsed in using excel find functions and all.
Tell me one thing if we want to use specific excel features using excel only then is it possible to do so using uipath ?
If suppose there are two sheets and i want to compare all columns and all using excel functions and not VB dot net
Is it good enough technique to do so ?


#4

Hi mudit,

It’s usually a good idea to choose methods that work internally, because external steps (like keystrokes) add another thing you must rely on working. However, if it will make your process simpler and more efficient then I’d say go for it.

If you would prefer, you can create an Excel formula, then use “Select Range” and “Write Cell”, followed by another “Select Range” and a fill-down key-combo: TypeInto “[d(ctrl)]d[u(ctrl)”

And, your range you could find using the datatable.Rows.Count-1 or Array.IndexOf(datatable.Rows,datarow)

With the formula, you can get the results you need fairly fast.

You can also create a VBA macro and use Execute Macro, but it requires additional logic to manage that file.


#5

I am so sorry . I did not get your point of view.
Please refer below to my doubts:
1.The requirement is i should open an excel file :
a.Convert that file into a table
b.Use Filters Available in Excel to filter data.
c.Use fuzzy add on in excel to compare records.
d.Whatever are the results i have to do further processing on it.

Please let me know whether for these kind of process is UiPath reliable enough to to these kind of automation ?
even if we go ahead with UiPath what are the challenges in terms of reliability,accuracy and speed ?
What if we implement same logic using excel macros ?
I want you to draw a comparison between the two options.


#6

This is from my observation,

Excel data manipulation is most fastest, most reliable, and most versatile through .vbscript and .vbapplication coding, because it let’s you access every Excel feature directly. For example, you can use .Cell() to get cell information and you can use .Find() to quickly move around the spreadsheet. Loops are also faster and can process thousands of lines quicker.

However, the disadvantages to this is that it’s less sustainable and require more knowledge of these script languages. VB is also more difficult to work with websites (but doable), so the consideration would be to use multiple tools that work together, but that in itself adds complication.

With UiPath, you have the ability to work with vb.net query expressions, which let you find and compare information in datatables and arrays instantaneous. This is very reliable and resourceful, however does require knowledge of such methods.

From what I can tell, doing other things in Excel to access features require that you use click or keystroke actions so it’s more indirect. I have been able to make this reliable, though, using the ALT-key combinations that work in Office Applications.

The biggest challenge with UiPath and RPA in general is error handling, because when dealing with element selections on the screen you need to figure out what to do if something happens where the window is different. It takes a while to learn how to do this properly.

So in conclusion,
UiPath gives you the tools to develop what you are wanting, but VBS and VBA will “technically” be more stable (while sacrificing process documentation and sustainability). In my work environment, I have found that UiPath is very useful for Business Process visualization and organization and is reliable enough once the initial challenges of the learning curve have been overcome.

Sorry, if this isn’t the input you were looking for. :grin:

If your client is interested in logic similar to the .Find, this can be done in vb.net query within UiPath. The other option would be to create a VBA script in a macro-enabled file, or .vbs script which let’s you double-click to run it and does not rely on the macro file. It’s really up to you and your client, to be honest. It might depend on what you are comfortable with coding or if you need to access Web interfaces then maybe UiPath is the best option.

Regards.

CM


#7

Thanks a lot for detailed suggestions.Can you advice on below requirement ?

So i can details about the requirement.
I have two excels:
excel1-contains a column called employerName
excel2-contains a columns called as sEmployerName
Requirement - for each employerName in excel 1 we need to find a similar match in excel2 in sEmployerName column.
Currently what i did was i tried to find that whether the full employerName in excel1 is found in sEmployerName of excel2 and if not i tried to break the employerName from excel1 into words and then tried to find if either any word from employerName was found in sEmployerName .
But this not what is required.
Now we need to find similar matches from employerName in excel1 with sEmployerName in excel2