Excel data validation

Help
1

form2.xlsx (23.6 KB)
Dear all::grin:
Hi everyone. I met a complex excel case and I have thought it over and over again but I didn’t find a good way to do it with Uipath. Is there anyone can help me about it if convenient? It would be very appreciated.
It is an excel workbook called form1 with a lot of data, and I need to do many logic calculation of its column and also related to form2. Attached are form1 and form2.
Below shows the detail, I am sorry it is quite long, thanks for your patience to read it:
For those data in form1, each row of them has to meet the following requirement:
1, Data of Column A ”所属公司” in form1 equals Column B“COMPANY” in form2, column B“门店号” in form1 equals column A “USCODE” in form2, and Column Q “收款账号” in form1 equals column C“ACCOUNT” in form2. Moreover, there are 19 different “COMPANY” and each of them has a series of “USCODE” in form2, so in form1, I have to make sure those “门店号” (which is “USCODE” in form2) belongs to the specific “所属公司” (means “COMPANY” in form2), rather than other ones of “所属公司” (this mapping relationship of “所属公司” and “门店号” in form1 needs to be the same as “COMPANY” and “USCODE” in form2). Then, give a message of how many rows meet this requirement. For those data that isn’t qualified, write down them in another excel sheet.
2, in form1, data of column D “供应商” has to be “智龙”, for those not, write down them in another excel sheet.
3, in form1, for column G“订单状态”, if data of some row is “退款完成”, if the datetime of column M “请求提交时间” is not on the same day of column N “平台成功时间”, then data of this row in column P “平台金额(元)” needs to be smaller than zero, if not, write it down in another excel sheet as exception.
4, in form1, each row needs to meet this requirement: Column P “平台金额(元)”=Column H “用户在线支付交易金额(元)”+Column J “平台承担费用(元)”-Column L “平台佣金(元)”, for those not, write down them in another sheet.
5, check column E “订单ID” and column G “订单状态” and column P “平台金额(元)” to make sure there’s no same rows in sheet1, if there is, select one of the same ones as exception and write it in another sheet.
6, in form1, make sure Column R “账单日期” is 2017/4/23, if not, write it down in another sheet.
The above are all the details.Thank you very much.

form1.xlsx (1.4 MB)

2

Can you please provide your form1 data in English?

-Arun

3

Ok, there’s a couple ways to do compare columns of multiple tables.
First you want to use Read Range for both tables and store them into datatable variables.

1 method is to use a For Each Row (for first table) with another For Each Row inside it (for second table), and If Activities to check if the values are equal. I would suggest looking up some tutorials or the forums on how to utilize the For Each activities with tables.

2nd method and quickest is to use query functions to filter down the rows that equal those values. For that you will need to use the .Select() function and store the filtered rows into an array of rows. Then, you can get a count or loop through them with a For Each pretty quickly. On how to get the .Select() working you could search google or the forums possibly.

Sorry, I could not go in depth but hope this gives you some ideas.
Thanks.