How to loop a filter activity studiox?

Hi there,

I need to filter a specific column. In that coulmn, i need to use the whole items individually to populate a sheet. With that sheet, i need to open an excel and save and email. Kindly advise steps. Thanks!

@Mark_Rajkumar

  1. Filter Datatable will filter the required column
  2. For loop can be used for loop if you want to loop through each row
  3. No sure what you mean individual sheet and excel…But to write to excel you can use use excel file and write range or write cell as you need
  4. Send Email is an activity that you have already which needs to be used inside outlook app scope

If you dont see any actiivyt…click on activity → filter icon → select developer

Hope this helps

cheers

Hi @Anil_G what i was trying after I filter a column, I need select one by one in the dropdown to copy and paste to another sheet. Can advise how to use for each row in this studiox

@Mark_Rajkumar

So After filter you want to copy all the filtered rows into another sheet right?

You can do it together using write range you need not use loop unless you want to paste the data into different columns

And if already data is present in excel then we can use append range

For each row …provide the filtered datatable as input and then inside use excel file and then write cell if you want to write indivudual cells. Write range or append range if you want to write all the rows together

Hope this is clear

cheers

Hi @Anil_G I use filter datatable. In the filter datatable i keep a specific column. With that input, i use for each row. Than i read a range and write it to another sheet. Can you correct me if i am wrong.

@Mark_Rajkumar

This is correct

You should use the output not input

I am not sure why you are reading inside a loop…and also if you want to rite data from one sheet to another…unless there is some condition that you need to check again you need not loop…you can use append range or write range as per requirement

Hope this is clear

cheers

Hi @Anil_G i manage to do as per above except it is not take the individual filter dropdown list as attached

For example, i want to click Alice and than copy and paste it to new sheet. Repeat this for other names. Is there any method for this?

@Mark_Rajkumar

Follow the steps…

  1. Read the data from excel into datatable dt
  2. Use set variable with newdt on ledt side and dt.DataView.ToTable(True,“Namescolumnname”) this will give you unique values in the datatable.
  3. Use a for each row in datatable on this newdt
  4. Inside that use a filter datatable on dt with thhe column name as name column and value as currentrow(0).Tostring
  5. Now use write range and give the sheetname as currentrow(0).Tostring and data as filterddt

Hope this helps

Cheers

Hi @Anil_G,

I tried but unable. This applies to studiox?

  1. Read range the data into datatable and name is dt.
  2. Use save for later to create a variable called newdt where the value dt.DataView.ToTable(True,“Namescolumnname”)
    3.For each row, use newdt as output, (however it is not recognising the variable to insert)
    4.filter datatable inside for each row?
    5.as for write range, give sheet name as mention?

Hi @Anil_G

If u can draft out an example using studiox it will be great.

@Mark_Rajkumar

Use the set variable activity open advanced editor and you can write anything inside that

Did that throw any error?

Cheers

@Anil_G yes it throws an error

@Mark_Rajkumar

Most probably its because of this change.try this

Dt.DefaultView.ToTable(True,"Name")

Cheers

This also throw error as it mention ‘Dt’ is not declared @Anil_G

@Mark_Rajkumar

Check this…

DT is variable you have to create

ReadExcel (2).zip (46.9 KB)

cheers

1 Like

Hi @Anil_G i have already created a variable under a read range activity as Dt. The error still there.

@Mark_Rajkumar

Did you open my project…I did the same…May be your scope of dt is different or there must be soemthign wrong that you have done…else it would not throw error.

Please check the attached project in previous comment and modify it…those are the exact steps mentioned already

cheers

Hi @Anil_G i tried different sample using studiox but the variable cannot be saved.

@Mark_Rajkumar

Did you check My project…May be you need to do some trainings before doing the process…Becasue I gave you the process template as well and I am not sure if you opened it or not

And you might not see the variable because the scope might be wrong

cheers

Hi @Anil_G i am unable to see the process as my pc dont allow me to open the file. Can share screenshot of the process