How to compare, validate and update in 2 excel sheets simultaneously?

Help
, , , ,
1

BEFORE THE PROCESS:

Sheet 1 (BEFORE)

Col ID | Col Amount | Col Card Num

1 | $20 |
2 | $20 |
3 | $15 |
Sheet 2 (BEFORE)

Col Card Num | Col Card Amount | Col ID

786001 | $10 |
786002 | $10 |
786003 | $20 |
After the process is done, you’d like to produce the following sheets:

Sheet 1 (AFTER)

Col ID | Col Amount | Col Card Num

1 | $20 | 786001, 786002
2 | $20 | 786003
3 | $15 |
Sheet 2 (AFTER)

Col Card Num | Col Card Amnt | Col ID

786001 | $10 | 1
786002 | $10 | 1
786003 | $20 | 2

Here are the details:

Iterate through sheet 1 and pick up Col ID and Col Amount cell values from each row. For e.g. row1 in sheet1 has 1, $20 cell values.
-Go to Sheet 2 and check how many Col Card Amount rows are required to fulfill sheet1-row1-col amount($20). In this case 2 rows ($20=$10+$10) and then update Col ID with corresponding sheet1-row1-col id value i.e. 1 in sheet2.
-Similarly, update the corresponding col card number in sheet1.(sheet1-row1-col card number: 786001,786002).

I appreciate your help and support on the same.

Thanks & Regards.

2

Hi @sshussa,
Here you might have potential solution:
https://forum.uipath.com/search?q=compare%20excel%20sheets%20status%3Asolved

3

Sequence1.xaml (20.1 KB)

This is what I came up. But the issue this is just comparing it and updating it. I need Iterate through sheet 1 and pick up Col ID and Col Amount cell values from each row. For e.g. row1 in sheet1 has 1, $20 cell values.
-Go to Sheet 2 and check how many Col Card Amount rows are required to fulfill sheet1-row1-col amount($20). In this case 2 rows ($20=$10+$10) and then update Col ID with corresponding sheet1-row1-col id value i.e. 1 in sheet2.
-Similarly, update the corresponding col card number in sheet1.(sheet1-row1-col card number: 786001,786002).

1 Like
4

Any pointers please