Split cell value into multiple rows based on condition

Help Studio
1

Hello,

I am using below excel table. I am trying to create multiple rows after dividing quantity value by 2000.
In below example, quantity value is 31960. So, each row contains quantity value as 2000. Likewise 15 new rows will be created which sums the quantity 30000 and remaining quantity 1960 is created as new row again.

Current Scenario:

Item No Material Code Material Desc Plant Code Delivery Address Quantity Unit
31960 KGM

Expected Scenario:

Item No Material Code Material Desc Plant Code Delivery Address Quantity Unit
2000 KGM
2000 KGM
2000 KGM
2000 KGM
2000 KGM
2000 KGM
2000 KGM
2000 KGM
2000 KGM
2000 KGM
2000 KGM
2000 KGM
2000 KGM
2000 KGM
2000 KGM
1960 KGM

Appreciate your help to solve this problem.

Regards,
Purva

2

Read That Dt say DT1

Use Build Datatable Say DT2 and add all colums of DT1
UseFor Each Row DT1
Assign Quant=Cint(Row(“Quantity”).ToString)
while
Quant>2000
Then Add Data Row To DT2
AS first 5 columns blank say in Data Array = {“”,“”,“”,“”,“”,2000,"KGM}
Then use assign Quant=Quant-2000

out of while
Add data row Data Array = {“”,“”,“”,“”,“”,Quant,"KGM}

Then use Write range

1 Like
3

Hi @PURVA_KALE,

Create a new int32 variable called Quantity & assign total quantity.
Use a while loop with condition Quanty > 2000
Inside Do of while loop add 2 activities
1.Add data row with quantity 2000
2.Assign activity with expression quantity = quantity-2000
after while loop add an if activity with condition Quantity > 0
in then part add an add data row activity with quantity remaining

Hope this helps!

4

Thank you for your swift reply.

I have created workflow as per steps given but it is giving error for For each-- Object reference not set to an instance of an object.

Thanks.

5

can you share your workflow?

6

sample2.xaml (14.4 KB) test_rpa.xlsx (10.5 KB)

7

It Worked! Thank you.

I have taken for each outside excel application scope.

8

Great !\

You have marked wrong one as solution :sweat_smile: :sweat_smile:

Happy Automation!

9

Thanks :sweat_smile:

Happy Automation!

10

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