How to split an Excel sheet into two individual workbook based on whether it contains specific strings?

[thedriedseaweed88 (driedseaweed)]

I want to split an Excel sheet into two DIFFERENT workbooks based on the condition that a column contains a specific string. In my case, I’m looking at the “school” column. If the value is “ABC high school”, the entire row gets saved to a workbook1, if the value is “XYZ university”, the row gets saved to workbook2. Does anyone have any idea how to do it?

Excel sample:
Book1.xlsx (9.0 KB)

Ideal output:

[Gokul001 (Gokul Balaji)]

HI @thedriedseaweed88

Have you tried with Filter Data table activity?

Regards

[Sanjeev_Kumar4 (Sanjeev Kumar)]

Give the filter as contain for your specific column as shown in screenshot and use write range activity

[Yoichi]

Hi,

can you try the following sample?

dict = dt.AsEnumerable.GroupBy(Function(r) r("School").ToString).ToDictionary(Function(g) g.Key,Function(g) g.CopyToDataTable)

Sample
Sample20230927-7Lv2.zip (9.4 KB)

Regards,

[thedriedseaweed88 (driedseaweed)]

Hello, thanks for your reply! For the write range activity, how do I write the output results to a new workbook? Or create a new workbook to write in? Does Studio have this activity?

[Sanjeev_Kumar4 (Sanjeev Kumar)]

In write range activity give the file name you want, if file not exist it will create a new workbook file with your given name

[thedriedseaweed88 (driedseaweed)]

Thank you for your help! Your solution worked perfectly