How to cut the redundant row /

[Happydayyy (22035153d)]

Now the problem using this robot: After I filter the data based on “Type”, all the data of this “Type” will generate, as seen in the figure.

However, I only want to generate one row of data based on FA Number, as below template: (one FA Number only need one row)

May I ask how can I cope with this? I want the sample workflow as I can follow it up easier. Thank you!

Workflow:
FA final.xaml (64.8 KB)

[mkankatala (Mahesh Kankatala)]

Hi @Happydayyy

Give me some clarification on this, you want to remove the duplicates in the FA number column. Am I right?

If yes, then follow the below process,
→ Use Read range workbook activity to read the excel and store in a datatable called Input_dt.
→ Take an assign activity to write the LinQ Expression

- Assign -> Output_dt = (From row In dt.AsEnumerable()
                         Where Not row.IsNull("FA Number") AndAlso Not String.IsNullOrEmpty(row("FA Number").ToString())
                         Select row
                            	).Distinct().CopyToDataTable()

→ Use Write range workbook activity to write the Output_dt to the excel.

Or

Simply you can use the Remove Duplicates activity, it was an Modern design Excel activity
You have to use this activity inside Use excel file activity.

Check the below image for better understanding,

Hope it helps!!

[ppr (Peter Preuss)]

Please confirm our understanding
a duplicated row is rated on

• rows having the same values for all columns, but the Item column is excluded from the check

[Happydayyy (22035153d)]

Only one row of each FA number is shown, besides, may i ask after i cut some dupliate row, the item number need to change as figure.

[mkankatala (Mahesh Kankatala)]

Check the above reply I have given linq expression and Excel activities too. It will help you to delete the redundant rows in excel… @Happydayyy

Hope you understand!!

[ppr (Peter Preuss)]

We assume that the Project Compatibility is set to: Windows

@mkankatala

.Distinct().CopyToDataTable()

A → LINQ → Distinct Method will not deduplicate, if used against DataTable / DataTable Rows, but not the necessary Comparer will be passed as an additional function Argument

DataRowComparer Class (System.Data)

Unfortunately our Question on

@ppr

a duplicated row is rated on

• rows having the same values for all columns, but the Item column is excluded from the check

was not answered

Assumption: only FA numbers will be used for the DeDuplication

A sample flow like (Excel was read in into a datatable):

dtDeduplicated = 
dtData.AsEnumerable().DistinctBy(Function (x) x("ColA").toString.Trim).CopyToDataTable

Input Sample:

Part Result after Deduplication

Result after ID/RowNum Update

Can be used. Feel free to adapt it

If needed andthe result has be written back to Excel

• ensure that the Worksheet is cleared
  OR
• write it back to another Worksheet

[ppr (Peter Preuss)]

@Happydayyy
Can the topic be closed?

Forum FAQ - How to mark a post as a solution

Forum FAQ - How to mark a post as a solution This document is part of our beginners guide. This article will teach you how to properly mark a post as a solution. We were closely following our last UiPath Forum feedback round! topic and carefully extracted all the bits of feedback that was provided. As such, we would like to tackle the topic of solutions on our Forum and how to properly use them. When is the topic resolved? The topic can be considered resolved when the topic author has fo…

[Happydayyy (22035153d)]

I used this method, but I cant use the database variable I set to the range?

[Happydayyy (22035153d)]

[quote=“ppr, post:6, topic:719381”]

dtDeduplicated = 
dtData.AsEnumerable().DistinctBy(Function (x) x("ColA").toString.Trim).CopyToDataTable

May i confirm the following is right if i want to delete dulplicate FA Number row data
dtDeduplicated =
dtData.AsEnumerable().DistinctBy(Function (x) x(“FA Number”).toString.Trim).CopyToDataTable

[ppr (Peter Preuss)]

ColA is to replace by the ColumnName of the column, which is relevant from your data and will be used for the duplicate detection / calculation

[Happydayyy (22035153d)]

FA final + delete duplicate.xaml (65.2 KB)
Can you illustrate me how I can use this method? I am still confusing. Thank you so much!

[Happydayyy (22035153d)]

if i use the delete duplicate activity, May I know that what is the range should be in my example?

Many thanks!
Workflow:
FA final + delete duplicate.xaml (65.2 KB)

[Happydayyy (22035153d)]

When i use assign method. There is a error in this value to assign. how i can cope with this?

[ppr (Peter Preuss)]

we do not see any adaptions / isolated focused RnD implementation of our suggestions
but described how it can be modeled.

We recommend especially when exploring new things to apply the principle: Divide and Conquer

So:

@ppr

A sample flow like (Excel was read in into a datatable):

etc. can be used for the isolated exploration

[mkankatala (Mahesh Kankatala)]

Okay @Happydayyy

Here you are not using Remove Duplicates activity with in the Use excel file.
Kindly, Drag and drop Use excel file activity inside of it use Remove Duplicates activity.

→ Insert Use excel file activity and give the path of the file.
→ Inside Use excel file insert the Remove Duplicates activity and give the range as below,

Excel.Sheet("FA for 4N")

Check the below workflow for better understanding,

Hope it helps!!

[mkankatala (Mahesh Kankatala)]

I have modified your workflow check the below workflow image and do modifications in your workflow as I did,

If you find the solution, Make my post mark as solution to close the loop… @Happydayyy

Happy Automation!!