Copy cell value from one excel file to another based on condition in iterative way


#1

In above 2 files I need to do following :
a) In file A and B I need to check if value of column “Ver” is same then check value of “Rank” (in file A) and all values under columns 1-12(File B)
If value of Rank in File B (all those values which are greater than the highest value of Rank in file A) is more then copy all such values (add new rows) under respective column(1-12) in File A with same value Col1 and Col2. This should happen with all set of rows in both files.

Ex in above case for “Ver=AAR01” 2 new rows should be added in File A for each rank value (4-5 in File B) along with exact value of these ranks from File B.

Col1 Col2 Ver Rank Col5 Col6 Value
A B AAR01 4 176
A B AAR01 5 198

and for Ver=BBC01 , 3 new rows (as ranks 10,11,12 in File B are greater than highest value of Rank =9 in file A) should be added in File A:

Col1 Col2 Ver Rank Col5 Col6 Value
D E BBC01 10 34
D E BBC01 11 78
D E BBC01 12 34

How to check the condition and copy cell value from File B to File A. I am using datatables for both files but how to specify to pick specific cell value in iterative way. I checked previous posts but there’s no query like this.


#2

Hi @hlsumit14

Please find attached solution and check if it works for you.
ExcelTask.zip (22.4 KB)
Solution writes the output to temp.xlsx excel file.
Thanks


#3

Thankyou so so much Bharat … Yes this is what I want. I am just trying to understand the flow.
One query on same problem :
Is it possible to implement below as well in same flow . I tried this but it’s taking too much time to process/update may be due to so many loops in my code.

  1. In file B for each row, check whether any of the combination of column “Ver” and column (1-12) is present in File A. If yes then check if the value under that column is the same as in column “Value” of File A

If the value for any combination that matches in both files is different (Ex: in above case if AAR01+1 is 94 in File B but different in File A) then the value in File A should be updated with the value in file B) so in this case value for Ver = AAR01 and Rank = 1 in file A should be updated as below

Col1 Col2 Ver Rank Col5 Col6 Value
A B AAR01 1 94
A B AAR01 1 94

All the rows with same “Ver” and “Rank”" should be updated with same value from File B.


#4

Hi @hlsumit14

I have added one more sequence in the old solution at last as per your requirement.
Check if it’s correct and reduces execution time.
Please find attached updated solution.
ExcelTask.zip (23.0 KB)
Thanks


#5

Thanks Bharat. Yes it’s correct with minor correction .

Col1 Col2 Ver Rank Col5 Col6 Value
A B AAR01 1 94
A B AAR01 1 94

All other values are correct. Except above 2nd row. … All occurrences of combination should be updated with same value ie. 94 from FileB. I guess minor code update is required.

Once again thank-you so much Bharat. I am new to UIPath but really good to see so many experts like you here.


#6

Modified_Excel_Work.zip (74.4 KB)

Hey Bharat,
I added one small component (code & error is compressed) in order to update all the rows in File A with same value from File B but getting some error while assignment of total count. Is there anything I am doing wrong be it with datatype or something else is missed.
Can you please let me know where correction is needed.
Thanks in advance