Support Portal

for ProcessRobot and WinAutomation

Start a new topic

Filter data

I have plenty of data in excel, apparently I need filter data and copy to next worksheet.


Example :

Need filter "Place"(Col B) with "Yes" and cut the data and move to worksheet name"summary"


Can you advise the solution?


*Attached the sample of template

xlsx
(9.61 KB)

ADMIN

Hi Michelle,


You can get the excel data normally using the "Read from excel worksheet" action.


Once the data table is retrieved you can loop through the rows of the table, checking the filter condition (if the "place" equals/contains "Yes" etc.).

If the condition is satisfied, set the "summary" worksheet active and write the data row to it.


It could be something like this,




This is just a sample where I'm trying to paste the data row with "place" as "Yes" to the summary worksheet.


If you wish to cut the row from the existing worksheet, use the "delete row from excel worksheet" action.

Note that, while switching between the worksheets you'll need to specify which worksheet you wish to work with by setting it active.


Hope this helps.

Hi, I m tried but encountered error at steps "IF", attached print screen for your reference.


Next question : What if would like copy ColA data with "Place" is yes to worksheet "summary".Please advise.

docx
ADMIN

Hello Michelle,


For pasting into the "summary" worksheet you can specify which data you want in the write to worksheet action itself.

The variable %CurrentItem% corresponds to the entire row of data. If you want specific columns' data you can use %CurrentItem["ColumnName"]% to access it.


Regarding the error message, the attached screenshot is inconclusive as the error message is not completely visible.


Furthermore, if you need any support for handling issues and errors, please consider creating a new support ticket and our team will assist you accordingly. 

Have in mind that only users with an active SAP (Software Assurance Plan) subscription are eligible for our support services. 


Thank you and best regards! 

MIchelle - You can do this function using VBA within your .xlsx file, you can trigger the macro by the excel macro function in the application. 


Sheets("Sheet1").Select

    Rows("1:1").Select

    Selection.AutoFilter

    ActiveSheet.Range("A1:AZ" & lRow8).AutoFilter Field:=14, Criteria1:= _

     "=*@ericsson.com"

    Cells.Select

    Selection.Copy

   Sheets("Sheet2").Select

   Range("A1").Select

    ActiveSheet.Paste


Sheets("Sheet1").Select

    Rows("1:1").EntireRow.Select

Selection.Delete

    Selection.AutoFilter

Login or Signup to post a comment