How to apply Vlookup in two different Excel files for more than 5 columns by considering one column in one excel?

Help
, ,
1

How to apply Vlookup in two different Excel files for more than 5 columns by considering one column in one excel?Solution to this

2

Hmmm
Just now a thought
We can use multiple LOOKUP DATATABLE activity like this
—once after getting the datatable from read range named dt use For each row loop and pass the variable dt as input
—inside the loop use first LOOKUP DATATABLE activity and mention these in property panel
Input - your value to searched in the datatable
Datatable —datatable where the value is to be searched
Lookup column name - name of the column in datatable where the input value is to be searched for
Target column name - once after finding the value in the lookup column get the corresponding row value from this column
Out result - found value in a a string variable named str_output

Now next to this lookup datatable activity use another lookup datatable activity where in the input property mention this str_output variable as input
—the same with all property mentioned except with a change in lookup column and target column name

Like wise use n number of Lookup datatable activity and get the final result value

Or

To be simple and if know vb script then we can use that too in INVOKE VBA activity

Cheers @Priyanka_Sorate

3

I have attached two Excel Files.I want to apply vlookup on “Ext id” in second file and fetch data from first file i.e column B,C,D for 2 nd excel file

Excel1%20and%20Excel2
Excel1%20and%20Excel2608Ă—848 36.3 KB

4

Ok I will try for this.thanks

1 Like
5

Fine
Hope these steps would help you resolve this
—use excel application scope and pass the file path of first excel as input
—inside the scope use Read range activity and get the output with a variable of type datatable named dt1
—again use another excel application scope and pass the file path of second excel as input
—inside that use read range and get the output with a variable of type datatable named dt2
—now use a for each row loop and pass the above
Variable dt1 as input and change the variable name from row to row1
Inside this loop use another for each row loop and mention the input as dt2 and change the variable name from row to row2
Now use a IF condition like this inside the inner for each row loop
row1(0).ToString.Equals(row2(0).ToString)
If this condition passes it will go to THEN part where we can use assign activity like this
row2(1) = row1(1).ToString
And another two assign activity like this
row2(2) = row1(2).ToString
row2(3) = row1(3).ToString

We don’t need to use lookup for this case
Cheers @Priyanka_Sorate

6

Ok thanks .

7

Cheers @Priyanka_Sorate

8

I tried for above .But I require this process by using Vlookup.Please provide solution.

9

@Priyanka_Sorate
LINQ statement should solve the task. Let me know if its still open

10

Yes,I have also same query.Please provide this solution through VB using uipath…

11

@Sneha11 welcome to the forum
I can work out soon a sample but some requirements are to clear:

@Priyanka_Sorate
faster for me would be if you would share excel samples or data as text
second excel is empty on the 2nd, 3rd… cols
so your expected output are all rows and columns from excel1 except row with id 105 as it is not matching Excel 2. Am I right?

12

yes your saying is correct.I require this only in VB using vlookup concept.Thanks

13

@Priyanka_Sorate
give me some littel time I will work out within in a free slot.

14

Ok no problem

15

@Priyanka_Sorate
PFA sample XAML
Priyanka_Sorate.xaml (12.5 KB)

Just integrate it later into your implmentation and replace the Build DataTable with your logic for readin the Excels into DataTables. Take Care about the VariableNames and may adopt as well.

Ensure Following:

Let us know the result and any open questions. If its working so flag the solution post that others can identify quickly. Thanks

1 Like
16

I tried this one but another type of error is there so I am working on that &as early as possible I will convery about this.Thanks

17

@Priyanka_Sorate,

Use invoke VBA or Run Macro and invoke a macro that does it. :slight_smile:

18

@Priyanka_Sorate
I didnt get you. But lets sort out 1 by 1

  • My XAML is usable standalone, so you can inspect on how to realize and that it is working without macro or invoke code

  • is the error related to my xaml or anything else?

However post it and we will help you. Or just share your XAML and will have a look on it

19

By invoke VBA method it is done.but requirement is by vlookup.

20

Thank you .
Activity flow is as shown in attached file.It is executing but output is not generated

Activities
Activities1760Ă—898 72.2 KB
.From your solution ,little bit confusion about that.Vllokup is not used in this case.Our requirement is that vlookup should apply for this Scenario.

1 Like