Splitting Excel Data into Multiple Sheets Based on Unique Values

[Jaydheeeer07 (Dherick Jay Paalam)]

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!

[Manju_Reddy_Kanughula]

@Jaydheeeer07

check it here

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

[Yoichi]

Hi,

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.

Regards,