Need to group by column and want to store result in different excel sheets

Help
1

Hi Guys,

I have two excels employee details and department details, i want depart wise employee details in different excel sheet in one workbook.

Please help me out with grouping departments and how to store those results in different sheets.

Thank you in advance :slight_smile:

2

So ,…you want to consolidate two different workbooks in to single workbook (as sheets?)

3

yes i already joined them , now department wise employees should get into different sheets

4

Hi Buddy @amruta_pawar,

As you have the consolidated file as a single file, lets do one thing

  1. open the excel file with excel application scope
  2. use a read range activity to read the excel and save it to a datatable variable named out_dt
  3. Now lets take we have three departments like Mech,EEE,COMP under column deparment which is the second column
  4. Now to get those segregated we are going to use 3 filter datatable activity as there are three departments
  5. i would like to tell for one, kindly do the same for others as well
  6. Open the filter datatable activity with filter wizard open like
    image

where in columns detail i mentioned as in_dt.Columns(“department”) with condition contains “MECH”
7. Similarly buddy do for all the remaining
8. now you have 3 segregated datatables
9. While still being inside the excel application scope, use write range activity , but 3 times each with different sheet name as you need for each department with its respective datatable as input
image

  1. Thats all buddy you are all done…

Hope This would work…

Cheers…

1 Like
5

@Palaniyappan thanks to respond, so if i have 10 departments , i will need to filter and write range for 10 times ?

Is there any other work around or this is the only way?

1 Like
6

@amruta_pawar
Thats really a good question,

Yes buddy there is another way, but it takes certain condition and looping with department name array, comparatively though it looks bigger its simpler, to make above one more a bit easier we can pass sheets name as array value for each department instead of creating new activity for each dept

Cheers

1 Like
7

Is that working buddy…
Cheers…

1 Like
8

I am still trying but array mai distinct department ids kaise lu?Departments.xlsx (4.1 KB)
EmployeesData.xlsx (10.7 KB)

9

Buddy You mean you wanna have distinct array of departments…?

10

Yes and for those departments i need to generate different excel sheets with employee details.

11

You can group like this buddy @amruta_pawar

  1. Datatable dt=dt.DefaultView.ToTable(true, “DEPARTMENT”) in assign activity, which gives you a datatable out_dist_dept_col
  2. The use a assign activity with left side as
    Out_dept_list = out_dist_dept_col.Columns(“DEPARTMENT”).ToList()

Cheers buddy

12

i have to fetch employee details as well , If possible can you please find my attached xml and let me know how to solve it ,
Thanks in advance :slight_smile: saving data into multiple excel sheet.xaml (8.7 KB)

13

@amruta_pawar
Its resolved buddy

Here is your xaml zipped folder , pass the excel file name alone once and run the flow buddy…
saving data into multiple excel sheet.zip (17.7 KB)

Cheers…

1 Like
14

Hey Thanks buddy it works as expected, you rocks. :slight_smile:

1 Like
15

Cheers…Buddy Keep going…

16

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