Splitting Excel Data into Multiple Sheets Based on Unique Values

I’m using UiPath to prepare data from an Excel workbook for an automated task. I have a column called “Employee Name” that lists names, a column called “Job” that lists jobs, and a column called “Missions” that lists how many missions each person has completed. In the “Employee Name” column and the “Job” column, there are rows of names that could contain duplicates. See Picture 1

Therefore, I need to gather all of the distinct names in the “Employee Name” column, look for duplicate “Job,” add the number of “Missions,” and then write all of the values (Employee Name, Job, Missions) on a separate sheet with a name that corresponds to the value of “Employee Name” in the first column. As a result, there won’t be any duplicate “Job” values on the new sheet, and its corresponding value for “Missions” will be totaled. There should be three sheets, with each sheet containing Picture 2, and 3 as shown below, as there are three distinct “Employee Name”.

Thanks for the help guys!


check it here

Need to write in different sheet based on industry type, format type.csv - Help / Studio - UiPath Community Forum

1 Like


Can you try the following sample?

dict = dt.AsEnumerable.GroupBy(Function(r) r("Employee Name").ToString).ToDictionary(Function(g) g.Key,Function(g) g.GroupBy(Function(r) r("Job").ToString).Select(Function(g2) dt.Clone.LoadDataRow({g2.First().Item("Employee Name").ToString,g2.First().Item("Job"),g2.Sum(Function(r2) CInt(r2("Missions").ToString))},False)).CopyToDataTable)

Sample20230521-1L.zip (7.5 KB)

note : if you need to create full name’s sheet, please try to remove .Spit(" "c)(0) part in WriteRange.


1 Like

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