Reconciliation between two Excel files


I have
Excel 1 with 4 columns ( A B C D ) : The column D & C are empty fields
Excel 2 with 4 columns but have ( A E F G ) for example.
The “A” is the column in commun between the two datatables.

I want my bot to read the two datatables, and then enter in column “D” the values of column “G” when the data in column “A” is matching. if the column “G” is empty, enter in column “C” the values of column “F”

Thank you in advance!

did we try with lookup datatable activity @Kontra


I’ve just tried but i didn’t work, maybe be cause i didn’t enter the correct properties…
Input : DT1 (Excel1)
Lookup value : what should i write
Look up column : Column “A” of DT1
Target column : Column “A” of DT2 (Excel2)
Outputs : i don’t know…

–once after getting the datatable from read range named dt1 and dt2 use For each row loop and pass the variable dt2 as input
–inside the loop use first LOOKUP DATATABLE activity and mention these in property panel
Input - row(0).tostring
Datatable —dt1
Lookup column name - “column name of A from dt1”
Target column name - “column name of G from dt1”
Out result - found value in a a string variable named str_output

–now next to this LOOKUP DATATABLE activity use a assign activity like ths
row(“column D name”) = str_output.ToString

Cheers @Kontra