Can anyone help on how to solving this excel problem!

I have two excel files
1)Global sheet

2)Final File

where
I need to add the location which is present in global sheet excel file into the entity column in final file excel while comparing the names of employees but the column name “Employee Name” is present in one excel and “Name” is present in another excel
Kindly provide me a solution on how to solve it!

Hi @Kancharla_Mahija

If the data is not confidential could you provide the excels with dummy data.

Regards

@Kancharla_Mahija

You can try this linq in invoke code

Read both tables into dt1(final) and dt2(Global)…send dt1 as in/out and dt2 as in argument

Dt1.AsEnumerable.ToList.ForEach(sub(r) r("Entity") = If(dt2.AsEnumerable.Where(function(x) x("Name").ToString.Equals(r("Employee Name").ToString)).Count>0,dt2.AsEnumerable.Where(function(x) x("Name").ToString.Equals(r("Employee Name").ToString))(0)("Location").ToString,""))

Cheers

No there is no luck with this! but Thanks for your efforts

@Kancharla_Mahija

Did you write the data back to excel?

and are employee name and name are exactly matching?

ideally this should be working. Can you show what you tried

And a input and output would help

cheers

Can just let me know the steps I’ll follow them !
image


I have the excels into two data tables - Global_DT and Error_datatable can you help with these

@Kancharla_Mahija

Can you share what have you written in Invoke code. Can you share the query. I will help you resolve that.

Regards

Error_datatable.AsEnumerable.ToList.ForEach(sub(r) r(“Entity”) = If(Global_Dt.AsEnumerable.Where(function(x) x(“Name”).ToString.Equals(r(“Employee Name”).ToString)).Count>0,Global_Dt.AsEnumerable.Where(function(x) x(“Name”).ToString.Equals(r(“Employee Name”).ToString))(0)(“Location”).ToString,“”))

Hi @Kancharla_Mahija

In the Invoked arguments, give the below way and try.
Use Write Range Workbook and pass Error_datatable to write the output to new sheet.

Hope it helps!!

Ok will try this approach

1 Like

Okay, try this and let me know if it works… @Kancharla_Mahija

This is a standard database operation called a join. Use the Join Data Table activity.

@Kancharla_Mahija

Errordatatable should be in/out…please change…I have given in previous post also

Later write the datatable back

Cheers


I have tried it but it is not working!

Hi @Kancharla_Mahija

Give the direction of Error_datatable as In/Out then, it will work.

Regards


Tried it also but no luck!
but the entity field is not filled with location!

@Kancharla_Mahija

If the data is not confidential could you share the excel files.

Regards

Ok will share them with dummy data do you need the main.xaml as well?
Finalfile_dummy.xlsx (9.6 KB)
Book2.xlsx (10.8 KB)

Hi @Kancharla_Mahija

Try this code in Invoke Code:

Error_datatable.AsEnumerable.ToList.ForEach(sub(r) r(“Entity”) = If(Global_Dt.AsEnumerable.Where(function(x) x(“Name”).ToString.Equals(r(“Employee Name”).ToString)).Count>0,Global_Dt.AsEnumerable.Where(function(x) x(“Name”).ToString.Equals(r(“Employee Name”).ToString))(0)(“Location”).ToString,“”))

Invoked Arguments:

Output:

xaml:
Sequence14.xaml (8.3 KB)

Try running the above workflow. Change the excel files paths in Read Range and Write Range. Check and let me know if it’s working for you.

Regards

@Kancharla_Mahija

Did you first happen to read both sheets?
Are the names exactly matching?

We see the same is working

Cheers