How to do this automation Task in Excel?

I have a big excel file as like below document…

So my question is that how to do filter and sorting using this one file… and created different excel file like lenovo.xlsx, ibm p.xlsx , huawei.xlsx

Only filter on “Service Item Group Code” and need to create different different excel file with filter and sorting data

Already i created filter and sorting process with different file but filter another file is hidden but keep in file. But i want to delete another rows without filtered data

My expectation reports…

But in this reports exist another value in hidden mode but i want to delete.

1 Like

–like use read range activity and get the output with a variable oftype datatable named dt
–now use FILTER DATATABLE ACTIVITY and pass the input as dt and get the output as dt1 with filter condition we need, where dt1 is a variable of type defined in the variable panel with default value as new
–then we can use write range activity with sheetname we want and and input datatable as dt1

similarly for other filter as well
kindly try this and let know for any queries or clarification
Cheers @ARahman

@Palaniyappan already i created filter and sort activity, but problem is that when i open my generated new file then shows all the expected data but till now all data are existed in this new file. I want to only filter data with a new separated excel file.

1 Like

Yah the datatable given as input is dt and output one is dt1 a new datatable
And we are going to use write range activity with same excel or different excel file which we cAn mention with a new file name and padding dt1 as input
Cheers @ARahman

Check this excel file…

Filter & Sorting automation is ok but check it row serial no 1,2 then 5 existed the previous data.

I want to filtered data only.

Is it same excel file and same sheetname been mentioned while using write range
Cheers @ARahman

1 Like

Hi @lakshman,

I have an issue… can you please check out this workflow?


Please anyone can help me on this workflow, how to manage after filter & sorting all the unsorted value will be deleted.

It seems like the rows are hidden and you don’t want them, instead you need only the filtered records
—now fetching the datatable of the very first input datatable with all data in it using READ RANGE activity and get the output with a variable of type datatable named dt
—if we use FILTER DATATABLE activity with filter condition like this
“Service Item Group Code” in the column field filter wizard with condition as “Contains” and value as “Lenova”
And here is what is needed to concentrated
That is we would have mentioned the input datatable as dt in the filter wizard along with above condition and **now we should mention output datatable with different name based on the condition for example mention as dt_lenova which is a variable of type datatable defined in the variable panel with default value as new

Now this datatable will be having only the filtered data in it
Now use SORT DATATABLE ACTIVITY and mention the input as dt_lenova

No if we write this datatable to a excel with write range activity we won’t be having those hidden records

Kindly try this and let know for any queries or clarification
Cheers @ARahman

1 Like

Below example for you refer.[testFilter.xaml|attachment]
(upload://vidY4GmwpFa8xeuUJ2UE8mEVsAI.xaml) (7.2 KB) testFilter.xlsx (8.7 KB)

1 Like

testFilter.xaml (7.2 KB)

1 Like

This is a simpler method using the filter datatable activity (12.6 KB)

1 Like

Thank you very much~

Thanks to all of you for giving me idea this automation.

Best of task is @ronanpeter. Thanks

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