Get Data from other Sheet

[111153 (환민 제)]

I want to get email address from sheet2, and fill sheet1 Email column.
Which activities do I have to use?

help.xlsx (10.5 KB)

[prasath_S]

Hi @111153

Please try below steps,

1. Read the sheet1 data using read range activity and store it in the datatable
   Variable name dt1.

2.Read the sheet2 data using read range activity and store it in the datatable
Variable name dt2.

3. Use for each row activity and loop the dt1.

4. Inside for each row use lookup datatable activity and pass datatable dt2,lookup value as row(“Name”).tostring and column name as “Email”, in cell value create a output variable called result.

5. After lookup datatable ,use assign activity and give row(“Email”) = result

6. After for each row activity use write range activity and write the datatable back to the excel.

Lookup Data Table

Thanks

[Yoichi]

Hi,

Another solution:

Sample20210906-3.zip (10.5 KB)

Hope this helps you.

Regards,

[111153 (환민 제)]

Thanks for answering. But I have some problems…
To use this process for other one, ‘Assigne dict = dt2.AsEnumerable~’ makes an error.

‘Assign : An item with the same key has already been added.’
How can I solve this problem??

[Yoichi]

Hi,

@111153

‘Assign : An item with the same key has already been added.’

This error means there are duplicated Name items in Sheet2.
Can you check this? And if so, is the duplicated item correct for you?

Regards,

[111153 (환민 제)]

It had some duplicated Name, so I deleted duplicated names but same error occurs

[Yoichi]

Hi,

It shows duplicated items still exist. Can you check if there are some blank cells in Name column, for example?

or

Can you put message box with the following expression just before the above assign activity?

String.Join(",",dts.AsEnumerable.GroupBy(Function(r) r("Name").ToString).Where(Function(g) g.Count>1).Select(Function(g) g.Key))

This expression will show duplicated Name item.

Regards,

[111153 (환민 제)]

Ahhhh I thought blank cells are not duplicated things. There are blank cells, so I changed them and finally it works!

Second Assign makes errors, even though I have “EMAIL” and “NAME” row in dt1
Assign : The given key was not present in the dictionary.

[Yoichi]

Hi,

@111153

Second Assign makes errors, even though I have “EMAIL” and “NAME” row in dt1
Assign : The given key was not present in the dictionary.

This error means some Name items in Sheet1 does not exist in Sheet2.
If it’s normal, we need to put If activity to check it.

Regards,

[111153 (환민 제)]

If you have enough time, can you make ‘if condition’ for me??

[Yoichi]

Hi,

Sure. Here you are.

dict.ContainsKey(CurrentRow("NAME").ToString)

Regards,

[111153 (환민 제)]

Thank you It works!