How can I identify duplicate columns in my data table and remove it?

Help Studio
, , , , ,
1

Hi, currently I encountered a problem not sure how to solve. I’m new in UiPath, not so familiar with VB and LinQ. Did some research other similar post/problem but cannot really understand. Would like to seek help from expertise.

I extracted an excel file sheet1 as dt_Masterlist which contains multiple columns.
Example:
Column1: ID, Colmun2: Name, Column3: Parts, Column4: S value, Column5: Status, Column6: Pass Reason, Column7: Pass category.

Somehow I found out the Column1 ID have duplicate rows with same values in other columns except Column5 Status. Column5 Status shows one in “Pass” and one in “Miss”.

I would like to remove the duplicate ID as a row with status indicated “Pass”. Target is to make sure no duplicate in ID and indicate the status as Miss if the duplicates happened.

Please kindly help and happy to provide much more details if needed.

2

Hi @Marcellee

Can you share sample input and expected output.

Regards

3

Hi @Marcellee

Please try with this

  1. Read Range (Excel file) → dt_Masterlist

  2. Assign:
    duplicateRows = (From row In dt_Masterlist.AsEnumerable()
    Group row By id = row(“ID”) Into Group
    Where Group.Count() > 1
    Select Group).ToList()

  3. For Each (group In duplicateRows)
    a. Assign:
    passRow = group.Where(Function(row) row(“Status”).ToString() = “Pass”).FirstOrDefault()
    missRows = group.Where(Function(row) row(“Status”).ToString() <> “Pass”).ToList()

    b. For Each (row In missRows)
    i. Assign: row(“Status”) = “Miss”

    c. If passRow IsNot Nothing
    i. Assign: dt_Masterlist.Rows.Remove(passRow)

  4. Write Range (Excel file, Sheet name, Cell) → dt_Masterlist

1 Like
4

Hello Marcellee,

As per your concern you just want to remove Duplicate IDs with status “Miss”.
If we did this, you would have data which will not contain any duplicates.

Please confirm with this.

5

Yes, I would like to remove duplicate rows of ID with “ Pass” status

6

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