Check and Update in both excel sheets

Help
, ,
1

Hi Team,

Here is my requirement:

Sheet1 | Sheet2
Col ID, Col Amount, Col Card Number | Col Card Number, Col Card Amount, Col ID
1, $20 , 786001,786002 | 786001, $10, 1
2, $20 | 786002, $10, 1
3, $15 | 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).

Appreciate your help and support on the same.

Thanks & Regards

2

Hi @sshussa - Here are some steps which you can take.
Store the whole sheet2 in a Data Table say DT2.
In For each row(of Sheet1):
Get the value and store that value in some variable.
Then use filter data Table activity in DT2 for value you have store.
Update the value in Data Table(You can directly use excel to update value)

Hope it will help you.

Best!!
Anmol

3

I’d suggest using the Read Range Activities to pull this data into two separate DataTables, and then add any information you need as you work through the two DataTables.

From what I understand, the first Excel sheet is the “Master Sheet” that the data needs to comply with, and the 2nd Excel Sheet is used to determine the payment/payment sources and you want to cross-examine them to reflect this information in one another, correct?

For clarity, are you saying these are your two Excel Sheets 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 Amnt  | 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

Is this correct? It would help a lot to be able to clarify this, the question seems a little messy in it’s current form.

1 Like
4

Hello Mike,

Perfect. This is absolutely correct. Apologies for the confusion. Is it feasible to share the code for the same.
Appreciate your help and support.

Regards,
Syed

6

Team,

Any one would help me on this? As I am new to UiPath & RPA world. Your help will be kindly appreciated.

Thanks!

7

Team - Any pointers please.

8

Hi mike. if you know the answer please update with the solution.

thanks&regards