Multiple filter in excel by Modern Activity

Help Studio
, ,
1

i want to filter all column value and then he has to save that filtered data in new sheet in same excel file and again he has to filter another value from the given column this process should continue till the last value of given column to filter
i have tried deferent ways but i am not able to run that process again for next value
please help me for this

2

Hi @toufikmujawar ,
Could you please share your file and rules to filter or expect output?
My way is
1.read all file to get data table
2.base on your requirements we use filter data tale or LINQ…to filter and get new data filtered
3.write new data to new sheet in file

Regards,

3

@toufikmujawar

Welcome to the community

You can use the excel filter activity and use it inside a loop

Or

Read the data into datatable and use filter datatable inside a loop so that the filters you need can be done one after the other…

You can save the filters you need as key value pairs in dictionary where key is thw column name and value is the value to be filtered on column

Cheers

4

Hi @toufikmujawar

Excel Application Scope (WorkbookPath: “YourExcelFilePath.xlsx”)
Read Range “YourOriginalSheetName” → Output: dtOriginal

Assign uniqueValues (Variable Type: List(Of String))= dtOriginal.AsEnumerable().Select(Function(row) row(“YourColumnName”).ToString).Distinct().ToList()

For Each value In uniqueValues
dtFiltered = dtOriginal.AsEnumerable().Where(Function(row) row(“YourColumnName”).ToString.Equals(currentValue)).CopyToDataTable()

Write Range in “YourExcelFilePath.xlsx”

5

Main.xaml (15.0 KB)
Target.xlsx (52.9 KB)

6

Hi Sanjay,

Assign uniqueValues (Variable Type: List(Of String))= dtOriginal.AsEnumerable().Select(Function(row) row(“YourColumnName”).ToString).Distinct().ToList()

For Each value In uniqueValues
dtFiltered = dtOriginal.AsEnumerable().Where(Function(row) row(“YourColumnName”).ToString.Equals(currentValue)).CopyToDataTable()
HOW TO USE THIS ONE CAN YOU HELP ME

7

Hi @toufikmujawar

In the Assign Activity

  • To: uniqueValues (Variable Type: List(Of String))
  • Value: dtOriginal.AsEnumerable().Select(Function(row) row("ActualColumnName").ToString).Distinct().ToList()

For Each (TypeArgument: String)

  • For each value in uniqueValues
    • Inside the loop:
      • Assign (To create a new filtered DataTable)
        • To: dtFiltered (Variable Type: DataTable)
        • Value: dtOriginal.AsEnumerable().Where(Function(row) row("ActualColumnName").ToString.Equals(value)).CopyToDataTable()
      • Write Range (Optional) - To write dtFiltered to an Excel file

Remember to replace "ActualColumnName" with the actual column name you’re using. Also, ensure that you’ve declared uniqueValues and dtFiltered with the correct data types in the Variables panel.

This process will filter dtOriginal by each unique value in the specified column and store each filtered result in dtFiltered , which you can then manipulate or write to Excel as needed.

Hope this helps

8

Hi Sanjay,
i am trying with your method still the error is coming can you check where i am going wrong and the same way my friend is also tried the bot is running but he is copying only headers not filtered (column AGENT) data. this will be a great help for me.
Main.xaml (15.2 KB)
Target.xlsx (52.9 KB)

9

Hi @toufikmujawar ,
Edit the filter activity
image
Regards,

10

image
image763×214 21.9 KB

Un check read only

11

I have fixed it
“Clone.xaml”
MultiFilter.zip (56.9 KB)
I have edited

image
image729×305 29 KB

and
image
image754×374 29.6 KB

and
image
image751×232 25.9 KB

my output
image
image769×744 177 KB

Regards,

12

The output is coming in same sheet i want individual output in different sheet
Thank you so much for your help and efforts::slight_smile:

13

Each data mapping with each value in other sheet
image
1st sheet is
image
2nd sheet is
image

that’s right?
It’s too easy to edit
regards,

14

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