form2.xlsx (23.6 KB)
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)