Hello, I have a list of email address (in Excel Sheet1) that i would like the UiPath to search another excel sheet (in Excel Sheet2) for the “case number” related to the email address in sheet1 and return into excel sheet1 in a new column.
Thanks for the help!
1.use read range to read both excel. i.e. datatable1, datatable2
2.Use foreachrow on datatable1 for excel for email address
3. inside foreachrow filter datatable2 for email address and get case number using column name
4. write case number into datatable1 using write cell
Hie @mnurhaikal for this logic you can read bot sheet and save their output as dt1 and dt2
after this use 2 for each row and inside for each use 1 if condition if row1(“yourcolumnName”).tostring = row2(“Columnname”) if the condition match
then in then branch use write cell activity and pass the value like row1(“your columnn”).tostring and in the cell field pass the column name like (D) and increament it will row index so it will right in the exact where the condition got matched
cheers Happy Automation
Hi, i followed this and works just that I didnt not use the excel version as i find it hard to use.
Anyways, I faced an issue. The “For each row in datatable” activity doesnt seem to work. It just copy one email and finds that email’s case number. It did not search the case number for the other emails.
What should i put in the “write cell workbook” under the “cell”? As im using the old version, there is no auto increment button.
For example if i put “D:D” in the cell box, the UiPath will copy the case number for the first email and paste under “D” column in a never ending loop.
Hi @mnurhaikal
you can below steps to write the matched case number in excel sheet 1
Read two sheets, consider dt1 is sheet1 data and dt2 is sheet2 data
take one assign activity, create one integer var(intVar) and assign value 1
pass dt1 in for each row in data table
take one assign, in To section pass intVar and in value section intvar+1
Use Filter data table in that pass dt2 variable and in search field pass currentrow(“Columname”).tostring.
create one output var for filter data table activity(dtFilter).
take one if activity to check the data is dtFilter.rowcount >=1
then take values from dtfilter eaither with index or by column,
if you want get the data from dtFilter by column name you can use one more for each row in datatable for dtfilter,
by using activity you will get casenumber. in To create a variable(strCaseNumber) in value currentrow(“Casenumber”).tostring
in write cell activity, pass that strCasenumber to write the data and in range section, in which column you want to write you can mention that column ID
suppose you want to write dataa in D column then you can use in range section
“D”+intVar .
By following this you can achieve your process
Hi @mnurhaikal , please try this way
Now you can iterate over dtResult using For Each Row in Data Table and pull out Case Id.
Please note this method still works if you have multiple cases per email address.
Hello, thank you for the help!
I require more detailed assistance especially this part,
"take one assign activity, create one integer var(intVar ) and assign value 1
pass dt1 in for each row in data table
take one assign, in To section pass intVar and in value section intvar +1
Use Filter data table in that pass dt2 variable and in search field pass currentrow(“Columname”).tostring.
create one output var for filter data table activity(dtFilter)."
Thank you!
Hello, Im stuck at this part,
“Now you can iterate over dtResult using For Each Row in Data Table and pull out Case Id.”
How do i pull out Case ID? what do i put in the body of “For Each Row in DataTable” activity?
Thank you.
Can you send me your excel files, i will prepare xmal file that you can use in your code
adding zip file by creating xmal file you can refer this code, based on your requirement you can change values,
Testing_Forum.zip (705.7 KB)
@mnurhaikal yes you are right there is now auto increment in classic activity so for incrementing row the for each row you are using the first excel sheet where you want to write your output go in the property section create a Variable of integer which increment your index position for reference i will share a screenshot. forget about the error on the write cell because i’m using write cell without excel application scope activity .just to give you a demo how you can increment the loop
cheers Happy Automation
Hi, I tried doing this but it always shows the first row. It does not continue for the other rows.
Do i have to use the “Clear DataTable” activity?
@mnurhaikal , please post some screenshots showing the debug result dt.
How many rows does the dtResult have?
ashokkarale
Yoichi
Anil_G
sonaliaggarwal47
sven.wullum1
Hiba_B
Vincent_Nuestro
Bot_Psychology
A_Learner
Sairam_RPA