Segregate data based on column data and add new column

Hi,
I have a data in excel , and based on column name user query ( if it contain keywords like job, vacancy), I have to segregate data into two excels.
One excel consisting of rows having keywords and other excel consisting of rows not having that data.
Also I have to add new columns in segregated excels and delete some old columns.
Please check if you can help.Input data.xlsx (9.2 KB) Output1.xlsx (8.5 KB) Output2.xlsx (8.5 KB)

1 Like

Sticking with UiPath’s activities, you could do

ReadRange to obtain a DataTable from your Excel file.
FilterDataTable to obtain a DataTable according your criteria (you can also exclude columns)
AddDataColumn to add columns to the datatable
WriteRange to insert your filtered datatable data into Excel

repeat the three previous steps but with the complementary criteria.

Thanks Michel for your response.

I have already tried it.

But there are certain columns also which i need to enter in between first name and last name in segregated excel.

How to do that, as the datatable we get from filter table is together and also headers are not included in it.

Thanks

Well, I’ll do it with VB, I don’t know if you can do that with provided activities. You’ll find below an example relying on theses activities as much as I can think of.

As far as I know, the ReadRange activity preserves headers if you check the option.

  • ReadRange to get data from Excel

  • FilterDataTable to filter your datatable and get rid of unwanted columns

  • AddDataColumn to add any wanted DataColumn

  • With invoke code, rearrange column index and set new name if needed:
    dataTable.Columns("Lead").SetOrdinal(0)
    dataTable.Columns(0).ColumnName = "FancyLead"

  • WriteRange with option to add headers checked.

1 Like