I have started working on Ui Path and below is the issue i have been facing.FilterTableE1.xaml (18.4 KB) I have two sheets where sheet1 haves the customers with invoice details and sheet2 having customers with the document numbers.
I have to check the customer in sheet2 with the sheet1 and identify whether sheet2 customer matches with sheet1 and then check if it has a document number, if yes then i have to copy the sheet1 data of that customer and as well sheet2 data for that particular cust and write it in new sheet with row wise.
Could you please give me an idea to achieve this.Compare Excel sheets.xlsx (12.2 KB)
You can read the two sheets into two different datatables and then use Join Datatables activity with Join type as Inner .
Specify the column Names in the Column Table 1 and Column Table 2 and condition and specify the output Datatable say OutDT2 .
once you have it in output Datatable , use filter Datatable to remove the rows with Document number is empty . (select remove , specify the output Datatable same as the input Datatable)
If the above solution is not feasible ,
2) Build A Master Datatable of columns containing DT1, DT2.
read each sheet into data tables says DT1 and DT2.
Iterate through DT1 using for each row
2.1) put FilterWizard with Input Datatable as DT2 and specify output DT as tempDt2
2.1.1) give condition to check if the customer is matching and document number is not empty
2.2) put an if condition with tempDT.rows.count > 0
2.2.1) then using add data row add it to the master data table which u created
You can add one more column to the output Datatable using add data column ,
then use for each to iterate through the Output Datatable ,
then put the required condition and
use assign activity row(“Type”)= “matched” or row(“Type”)=Not matched
and then write into the excel
You can Use remove duplicate rows activity and provide the same datatable to input and output before writing it into excel . can you try this ? This will remove the Duplicate rows from the datatable