How to split multiple excels based on a total excel which contains department property?

hello friends:
there are total excel contains department property, i want to split it to multiple excels based on the “department” of the total excel.:grinning:
the total excel:
i want to split this table to different excels based on “department”. that is all. thank you for any kind help!

Read the Total Excel in a data table. For each department , apply filter , department =“department name” .

This way you will get a new data table from the original data table for 1 dept. Write this to a new excel file

Repeat the filter for each department and you can write on different excel for each department

hi neonova, thank you first.
because i am a fresh in UI-path. i also have some quetions.
“filter” means “filter data table” ? could you please give me a detail simple example?? thanks again!

yes you are correct. I can highlight the steps

  1. Use Excel Scope Activity
  2. Inside Excel Scope Use Read Excel Range Activity (leave the range as “” so it reads entire workbook)
  3. The Read range will give output as datatable
  4. Use Filter Datatable Activity and there put the condition Department =" Your department)
    5.This will give you output as a new data table .
    6.Now simply use write Excel Range Activity and give the datatable you got from filter activity as input
  5. Repeat for all dept. You can put your department names in an array using Assign Activity
  6. Then use For each to iterate (loop through the array) to generate seperate excel for each file

Try the above steps, if you get stuck ask help :slight_smile: . You will learn more

thank you very much, :smiley:i will try it.

hi neonova,:blush:
I have learn more from your help. I already try it and it run successfully. But I have some problems.
About the last two points in yours.:sweat_smile:

  1. use For each activity but it seams not work because total Excel doesn’t split into different excel.(only one department excel)
  2. use For Each Row activity to make “Department” as a variable value. And every new excel will have new file name.
    Thank you!

hi my friend :slight_smile:neonova,
maybe I ask the question is not so clear.
now I repeat the important again. please explain the following point:
6. 然后使用For each迭代(遍历数组)为每个文件生成单独的excel

I had try it many times but it split to only one excel. but not generate seperate excel for each file:disappointed_relieved:

thank you ! hope reply. I need your kind help!

Happy New Year :slight_smile:

In for each activity supply the department names in an array

like For Each Dept in Dept_Array (which will have {“Dept1”,“Dept2”,“Dept3”} for all ur departments)

Inside that place the filter datatble activity and the write excel activity scope

That way for every department it will filter the master data table and generate output in excel as you wanted

Happy New Year:grin:
I am so happy to receive your reply. I will try it. Thank you!