Key in Attendance Status by read the data from 2 Excel File using 'Read Range Workbook" activity

[Yong_Shun_Jie]

Hi all,

I have a attendance responses Excel File call “Attendance Responses” and I use ‘Read Range Workbook’ to read the data from “Attendance Responses”. There are two data inside the Excel File call “Full Name” and “Attendance Status” and I want to key in the data “Attendance Status” into another Excel File call “Attendance Submission”.

Moreover, the Excel File “Attendance Submission” have a column to key in the “Attendance Status” simply call ‘Attendance’ and a name list call “Name”, and I also use ‘Read Range Workbook’ to read the data, so if “Full Name” contains “Name”, then will key in the “Attendance Status”, else will be “ABS”. The ‘Read Range Workbook’ not allow open the Excel file so I use ‘Write Range Workbook’ activity to write the “Attendance Status” but it just write the “Name” into the column ‘Attendance’.(See the attached screenshot below).

Appreciate if somebody can help me !

[Yong_Shun_Jie]

Here is the screenshot that how after the ‘Write Range Workbook’ write the data looks like.

[Anil_G (Anil Gorthi)]

@Yong_Shun_Jie

You need to use a look up datatable activity on attendance response sheet…you are only using submission and not attendence response sheet at all

Use look up range activity and in column name give name and in response give attendance status …and the output of this can be given in assign with currentrow(“attendance”) = output of look up

And in look up value field give currentrow("Name").ToString

Cheers

[Yong_Shun_Jie]

Hi Anil
I did use “Read Range Workbook” for ‘Attendance Responses’ file and sorry can’t understand your reply because I just started learning Ui Path hope can explain a little bit more to me

Thanks!

[Anil_G (Anil Gorthi)]

@Yong_Shun_Jie

please check the flow and follow

BlankProcess - Copy (16).zip (90.7 KB)

cheers

[Yong_Shun_Jie]

Hi Anil,
Thanks for your reply, but It still can’t key in the attendance status correctly, maybe can you help me check which part is wrong?

Appreciate!

[Yong_Shun_Jie]

Here is the screenshot attached of the attendance that it key in wrongly

[Anil_G (Anil Gorthi)]

@Yong_Shun_Jie

Okay just now saw that the name is not an exact match

So instead of look up datatable use filter datatable activity and filter the row with name contains currentrow("Name").ToString

If condition will be filtereddt.Rowcount>0

On else side use currentrow("attendance") = filtereddt.Rows(0)("attendanceColumnName").ToString

Hope this helps

Cheers

[Yong_Shun_Jie]

I see. But how can I Type filtereddt.Rowcount>0 in the condition? It shows Error. Need to create new variable?

Thanks!

[Anil_G (Anil Gorthi)]

@Yong_Shun_Jie

Create a datatable variabe as filtereddt…and assign it to output table in filter

Then use in condition

Cheers

[Yong_Shun_Jie]

Hi Anil,
On else side I follow as what you type here but an error occur, see screen shot below.

Thanks!

[Anil_G (Anil Gorthi)]

@Yong_Shun_Jie

My bad…move the else side part to then side and in else side do abs

Cheers

[Yong_Shun_Jie]

Omg it finally key in correctly! But why if I put the CurrentRow name as (“Attendance”) in assign activity it will write the name again? After I change name to CurrentRow(“Name”) it solve, by the way thanks Anil!

Best Regards,
Yong

[Anil_G (Anil Gorthi)]

@Yong_Shun_Jie

Awesome glad it helped…

I had given basis on knowing you wanted attendance if name is needed then that change is needed…but logic remains same

Please close the topic by marking solution if reaolved

Happy Automation

Cheers

[Yong_Shun_Jie]

I see, Thank Anil and have a great day!

Best Regards,
Yong