Separate unique values from a column and write them on another sheet

[Jaydheeeer07 (Dherick Jay Paalam)]

I’m using UiPath right now to process data from an Excel workbook and get it ready for an automated task. My question is this: I have a column called “Employee Name” that contains people’s names and another column called “Job” that contains their jobs. There are rows of names that may have duplicates in the “Employee Name” column. In order to write the unique name, any duplicates, and its corresponding value from the “Job” column into another sheet, I need to collect all the unique names in the “Employee Name” column. In short, I want to separate and store all unique values on different sheets. There will be 4 sheets if there are 4 distinct values. Use the pictures as references.

[supermanPunch (Arpan)]

Hi @Jaydheeeer07 ,

There are many posts here on the same type of use case, which we could handle better by using a Group By clause. Check the below which is one of them :

Split Csv file into multiple files according to month in date column

@Diva_P Welcome to the forum we can do it with help of GroupBy As a starter help: Assign activity: leftSide: TableList | DataType: List(Of DataTable) Right side: (From d in YourDataTableVar.AsEnumerable Group d by k= CDate(d(“Date”)).Month into grp=Group Select t=grp.CopyToDataTable).toList For Each Activity (NOT for each row) | TypeArgument: DataTable item in TableList write out month split excel to Excel - e.g. with write excel

Although the data might be different the concept and methods to be used are the same.

Let us know if you still need more help

[arjunshenoy]

Hi @Jaydheeeer07, welcome to the Community.

You can group unique data & write each group in a new sheet. You can use the following query:

(From row In dtData.AsEnumerable()
Group row By type = row("Type").ToString.Trim Into Group
Select Group.CopyToDataTable).ToArray

This query will give you an array output, which is an array of datatables. You can use a For Each loop to iterate through this table & write the data in seperate sheets. To name the sheets, you can use

Split(currentRow(“Employee Name”).ToString,” “)(0).ToString

This will name your sheets as Naruto, Sasuke, Kakashi, Madara, Gaara, etc.

Hope this helps,
Best Regards.

[Vishnuraj_Pandiyarajan2 (Vishnuraj Pandiyarajan)]

hi @Jaydheeeer07

Welcome to the Community!

Step:1 Employee Name as list/array

Step:2 drag For each activity and mention the list/array values

Step:3 u can take Linq Query to execute the datatable quickly

Step:4 then make a write range for your output sheet

Note: If you want to Mention First name of Employee name u need to mention in the Sheet name like below
currentItem.ToString.Split()(0)

I have attached the Xaml for the reference

test.zip (11.5 KB)

Hope this will help you !

Thanks Man
VP

[iamNairda (Adrian)]

Even though you have a pretty good amount of solutions I would like to add the “modern excel activity” way.

Brief explanation and the .xaml file is attached below:

• Add “Excel Process Scope”
• Add “Use Excel File within Excel Process Scope”
• Read Range of your Excel Input File
• Use “DefaultView” to get distinct values
• Use for Each row in new DT Distinct to create new Sheets, Filter the Input Datatable and Write Data dynamically back to Excel
• Clear filtered DT as we do not want any data from previous iteration.

Shuriken.xaml (15.8 KB)
I used the exact same DT as you did.