Data table merging, lookup text, and combine multiple datarow into the same row cell

Hi there, I have three data tables as attached. I do not know how to set the activities to produce the desired result as attached.

The steps required:

  1. If you open DT3, you can see there are only partner numbers without corresponding partner names. The Corresponding names could be found in DT2. I need to look up the names in short form (I.e. Lookup value is Partner Code; it should return corresponding partner names in short form to DT3)

  2. Then, if you open DT1, you can see the BankNo in Column C. The BankNo is also in DT3. However, there could be more than one BankNo in DT3 referring to only one row in DT1. What I need to do is - for all partner names and corresponding percentage with the same BankNo in DT3, write to the row in DT1 in which the BankNo matches, and using semicolons to separate the partners. Please see the desired result file for your reference.

How can do it? Much appreciate your help!!!

DT1.xlsx (63.4 KB)
DT2.xlsx (10.5 KB)
DT3.xlsx (9.5 KB)
DT Desired Result.xlsx (63.6 KB)


testkot.zip (1.0 MB)

1 Like

Hope this helps you…

Hello @Gresilda_Balla

Thank you for your help. However, it seems that something is missing? Any package I need to install?

Current dependencies
Dependencies




Variables

Assigns:
BankNo_DT1=Row_DT1("BankNo “).ToString.Trim
PartnerCode_DT2=Row_DT2(“Partner Code”).ToString.Trim
ShortForm_DT2=Row_DT2(“Short Form”).ToString.Trim
BankNo_DT3=Row_DT3(“BankNo”).ToString.Trim
PartnerCode_DT3=Row_DT3(“Partner Code”).tostring.trim
Percentage_DT3=convert.ToDouble(Row_DT3(“Percentage”).tostring.Trim)*100
PartnerAndPercentage=PartnerAndPercentage+ShortForm_DT2+” “+Percentage_DT3.ToString+”%; "
PartnerAndPercentage=PartnerAndPercentage.Remove(partnerAndPercentage.length-2)

Cell:
“I”+(Index_DT1+2).ToString

1 Like

If you don’t understand anything feel free to ask me again.

1 Like

This is absolute genius. I understand completely! I am surprised it only takes 2 seconds to complete, given that the robot has to process 24x12x35 rows… Many thanks for your help!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.