Add column with condition without loop

Help Studio
1

|Roll No|New Val|
|10390|A|
|20987|B|
|30965|C|
|10987|A|
|30764|C|
|20864|B|

Based on roll number starting from 10 , 20 or 30 I need to add new val A B or C without loop.

1 Like
2

Without Loop…Now you need to use Linq.

3

Hi @SakshiGupta ,

can we use excel formula to achieve this requirement without loop.

Sample Formula: =IF(ISNUMBER(SEARCH(“10”,A2)),“A”,IF(ISNUMBER(SEARCH(“20”,A2)),“B”,IF(ISNUMBER(SEARCH(“30”,A2)),“C”,“”)))

Use below steps in Uipath:

  1. Use excel application scope to open the excel file which contains only one column with RollNo.

  2. inside excel application scope use write cell activity to create header for New val → A2 = “NewVal”

  3. Use write cell value to write the formula in the B2 Cell.

“=IF(ISNUMBER(SEARCH(”+CHR(34)+“10”+CHR(34)“,A2)),”+CHR(34)+“A”+CHR(34)+“,IF(ISNUMBER(SEARCH(”+CHR(34)+“20”+CHR(34)+“,A2)),”+CHR(34)+“B”+CHR(34)+“,IF(ISNUMBER(SEARCH(”+CHR(34)+“30”+CHR(34)+“,A2)),”+CHR(34)+“C”+CHR(34)+“,”+CHR(34)+" “+CHR(34)+”)))"

  1. Use Auto fill to auto fill the same formula to populate in all rows in second column in single shot.

i am not very fond of linq. since we can use the above method to implement the same without any loop. please try and let us know. thanks.

1 Like
4

@SakshiGupta

Use Write Cell activity and use this formula

image

After that you can use Fill Range and pass the Range to fill the above formula

Hope this may help you

Thanks

2 Likes
5

Hi @SakshiGupta,

Your request of “Without loop” is not achievable. Such a thing does not exist. Without iteration none of the approaches will work.

Standard for each row activity is more than sufficient for your case.

Standard UiPath activities are not slow. Please read this excellent thread / test from @postwick to see the test results.

1 Like
6

Yeah I agree with you @jeevith . Whatever approach we would use it will take some time in background and process it. Thanks for your note.

7

the range will be dynamic how to overcome that

8

my bot will process more that 40000 rows , the rest of the process is not taking time but this part is consuming a lot of time. how to over come that?

9

my bot will process more that 40000 rows , the rest of the process is not taking time but this part is consuming a lot of time. how to over come that?

10

Can you attach xaml file

11

@SakshiGupta

You can use DataTable.Rows.Count

which will give you the entire count of Rows If the columns are fixed let’s suppose D

Then “A” + DataTable.Rows.Count +“: D” + DataTable.Rows.Count

This will act dynamically

Hope this will help you

Thanks

12

getting error autofill range of class fail

13

it worked thanks… reduced the time

1 Like
14

Sure. please find the attached work flow it is having the steps i suggested above. please try and let us know. i am sure it will not take so much time for 40000 rows. please test it and let us know. thanks.

Excel FormulaDemo test.zip (9.2 KB)

15

You can’t overcome it. It’s 40,000 rows. More rows = more time.

1 Like
16

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