Check for duplicate values in Excel

Learn Academic Alliance
1

Hi Team,
I have an excel in which i need to check that row(1) values should not be equal to values present in the columns of Row(2) inshort i need to check that if there are any duplicate values in the excel sheet then BOT should color the Column"k" i.e. comments column with Purple.
I have applied below logic to check Duplicates but it doesn’t seems to be working.

row(“USER EMAIL (IF APPLICABLE)”).ToString.ToUpper.Equals(inputListDT.Rows(0)(“USER EMAIL (IF APPLICABLE)”)) and row(“APPROVER EMAIL”).ToString.ToUpper.Equals(inputListDT.Rows(0)(“APPROVER EMAIL”)) and row(“USER NT LOGIN”).ToString.ToUpper.Trim.Equals(inputListDT.Rows(0)(“USER NT LOGIN”)) and row(“USER FULL NAME”).ToString.ToUpper.Trim.Equals(inputListDT.Rows(0)(“USER FULL NAME”)) and row(“APPROVER NT LOGIN”).ToString.ToUpper.Trim.Equals(inputListDT.Rows(0)(“APPROVER NT LOGIN”)) and row(“APPROVER FULL NAME”).ToString.ToUpper.Trim.Equals(inputListDT.Rows(0)(“APPROVER FULL NAME”))

then
writecell “Duplicate”
cell value: “k”+counter.tostring
Set Range Color “system.Drawing.Purple”

2

Hy @som17 ,

I believe your solutions sounds a bit too complex, difficult to understand.

How many columns must be duplicate? Can you upload here a mock file so I can have a look at it?

Regards

3

input.xlsx (11.5 KB)

Hi,
Please find attached input file and in this file i need to check there should not be any duplicate rows…we need not to consider RES1,RES2,RES3 column values…
if there are any duplicate rows then in “k” column BOT has to write
“Duplicate” for those particular rows and color it Purple just like it has done “Yellow” for “Blank”.
please check and support.

4

@nadim.warsi @RobotMaster anyone on the forum…please suggest

5

@SenzoD

1 Like
6

@msan can u please help on this?

Regards
som

7

Hello @som17 ,

Can you please confirm the following:

  • We can sort the data by “USER NT LOGIN”
  • We can consider a duplicate if “USER NT LOGIN” value is the same.
8

Hi,
i have attached excel sheet and logic that i have used to check duplicates:
row(“USER EMAIL (IF APPLICABLE)”).ToString.ToUpper.Equals(inputListDT.Rows(0)(“USER EMAIL (IF APPLICABLE)”)) and row(“APPROVER EMAIL”).ToString.ToUpper.Equals(inputListDT.Rows(0)(“APPROVER EMAIL”)) and row(“USER NT LOGIN”).ToString.ToUpper.Trim.Equals(inputListDT.Rows(0)(“USER NT LOGIN”)) and row(“USER FULL NAME”).ToString.ToUpper.Trim.Equals(inputListDT.Rows(0)(“USER FULL NAME”)) and row(“APPROVER NT LOGIN”).ToString.ToUpper.Trim.Equals(inputListDT.Rows(0)(“APPROVER NT LOGIN”)) and row(“APPROVER FULL NAME”).ToString.ToUpper.Trim.Equals(inputListDT.Rows(0)(“APPROVER FULL NAME”))

if you have any other logic…then please share

9

Hy @som17,

What column or combination of columns must be duplicate for the robot to analyse?

Regards

10

Add a column “DUPLICATE” to contain your mark.

The idea is to iterate the table from the last occurence to the second. The row is compare to the previous one.

\\ Assign (Array of String)
keys = {"CAMPAIGN NAME", "USER NT LOGIN", "APPROVER NT LOGIN"}

\\ Assign
idx = dt.Rows.Count - 1

\\ While
idx > 0

    \\ ForEach
    key in keys
    
        \\ If
        dt.Rows(idx)(key).ToString.Trim = dt.Rows(idx - 1)(key).ToString.Trim

            \\ Assign
            dt.Rows(idx)("DUPLICATE") = "Duplicate"

            \\ Break

    \\ Assign
    idx = idx - 1
3 Likes