Read the last 3 rows in a column based on condition and copy values

Help Studio
1

I have a use case when I have an excel in downloads folder and I read it using condition that the file name starts with “Intra_Day_Pattern_”

Once done, I read sheet1 and edit it to remove the row that contains Total and save it in the variable ‘FiltSheet’

Now what I want to do it, check the last 3 rows in the column ‘E’ which has values (in my case it is E10 to E12)and if values in all these 3 cells are less than “-10” (which is valid in my case as per the attached excel sheet) then copy these three cell values and past it in the sheet1 of another excel workbook named ‘macros’

I have attached the flow until I have worked upon and also the excel sheet.

Could anyone suggest?

Main.xaml (13.2 KB)

Intra_Day_Pattern_1690371293486.xlsx (10.0 KB)

3

@girishankar09
Try this below xaml
Datatable.xaml (18.0 KB)

i have used Write range workbook , kindly replace the activity into modern activity

4

It gives an error. So the thing is Variance % is not a header, it is not in the row 1. insted can we use column number by replacing something. Also I need to check only last 3 rows, not sure which activity makes sure to check only last 3 rows of the column
image

5

  1. To get last three rows.
    You can use Read Cell workbook and cell is “E” + (dt.RowCount + 1 - Index)
    So in first loop you will get last index, next loop second last and so on.

  2. In the loop once you get the value, you can check if, Cint(cellValue) < -10. Then increment a counter value and store the cellValue in an array (Say colArray) if result is true.

  3. After the loop, if counter value is 3, Then write the colArray to macros

  4. If you want to right the values in last 3 column, if there are less than -10, i.e( If E10 is less than -10, E11 isnt less than -10 and E12 is less than -10). Then using step 2, when you check Cint(cellValue) < -10 and use write cell workbook where workbook path or sheet name is macros and value is the cellValue which you got from last rows.

6

@girishankar09 ,
Try this
New folder (2).zip (18.8 KB)

Hope it helps you