How to cut the redundant row /

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)

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!!

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

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.

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!!

We assume that the Project Compatibility is set to: Windows

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

Unfortunately our Question on

was not answered

Assumption: only FA numbers will be used for the DeDuplication

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


grafik

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

Input Sample:
grafik

Part Result after Deduplication
grafik

Result after ID/RowNum Update
grafik

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

@Happydayyy
Can the topic be closed?


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

[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

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

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!


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)


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

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:

etc. can be used for the isolated exploration

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!!

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!!

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