Excel operations with row indexes

Help Studio
, ,
1

Please find attached screenshot
image
If E2 is blank it should pick A2 and B2 and write sum value in C2 and division value in D2
If E3 is 2x then it should pick A3 and B3 and write sum value in C3 and division value in D3 and so on
So it will come under one enitre loop?
also do we need to change variable type to Matchcollection for values in excel??

2

Hi,

What you can do here is

  1. Read the Excel into Datatable
  2. Use for Each row
  3. Loop the rows and perform the specific operations as per business logic and update the values back in the same DT
  4. once you are out of the loop , write back the DT to Excel
3

@Pogboom

Follow the below steps

  1. Use open excel and inside use for each row in excel
  2. Inside use if condition to do your checks… so to check the e2 values … currentrow("Var").Tostring will give the value
  3. Then inside the if condition use write cell and write the sum and division as needed
  4. In the for loop you will have a index property …create a variable for it and use it in write cell to change the rows …For Column B "B"+(index+2).Tostring index is the variable from for loop and adding 2 because index starts from 0 and one more for header row

Cheers

4

Hello @Pogboom, you could try something like this

image
I did not use your 2x, 3x, because it seems to me it still does the same?

you could change the flow to check with if conditions to see if there is a 2x, 3x etc and change what it will do

Kind Regards

5

here you are hardcoding the indexed like Byindex(0) and Byindex(1)
and depending upon 2x,3x it should pick its corresponding values
do you have any workaround for this?

6

not able to understand 4th point

image
image1054×493 24.9 KB

here i created variable by name of sampleindex where do i define this condition “B”+(index+2) ??

7

you wrote:
“if E2 is blank it should pick A2 and B2 and write sum value in C2 and division value in D2
If E3 is 2x then it should pick A3 and B3 and write sum value in C3 and division value in D3 and so on”

this sounds for me like you just want to do each row, which above flow does. It takes currentRow but same column.

But as I said, you can do an If condition before the write cell and check for your 2x,3x column value and set right columns to use before the write cell with assign activity.

Kind Regards

8

Basically you can write excel formula to the cells for those rules.
Then you can use write cell to write the first row on excel.
After that you can use auto fill range to fill other rows

9

@Pogboom

That is for range in write cell activity

Cheers