Delete Duplicate Excel Rows

Task Description

Automation Anywhere provides easy Excel Automation. For more information see Excel Automation Solution. This examples shows how to sort the data in Excel sheet and delete duplicate entries if any.

T29.atmx     TestData.xls

Note: Save the xls in C: drive, the .atmx file in My Documents -> Automation Anywhere -> Automation Anywhere -> My Tasks


Define Variables

  • Go to Variable Manager and create variables "C1", "C2", "N1" and "N2"

Create new task

  • Click on New->Task Editor.

Open Excel sheet

  • From Application Category->Excel, Select 'Open Spreadsheet' option.

Sort data in Excel sheet

  • Insert Keystroke command to sort data in Excel sheet.

    Keystrokes: [CTRL DOWN]a[CTRL UP] in "Microsoft Excel - TestData.xls"
    Keystrokes: [ALT DOWN]d[ALT UP] in "Microsoft Excel - TestData.xls"
    Keystrokes: s in "Microsoft Excel - TestData.xls"
    Keystrokes: [ENTER] in "Sort"

Go through all the cells until word 'STOP' found

  • Insert Loop for variable "Clipboard" not equal to "STOP".

Retrieve value of 1st cell

  • Insert Excel command to get the value of the first cell

Check for duplicate value

  • Insert a Loop to check value for currently active cell is equal to that stored in the variable "C1"

    Loop While $N1$ Equal To $C1$

Check if condition is satisfied

  • i.e. Current active cell (C1) is equal to next active cell (N1) in line 1 and current active cell (C2) is equal to next active cell (N2) in line 2 and so on.

    If $n1$ Equal To $c1$ Then
    If $n2$ Equal To $c2$ Then

Delete the duplicate row

  • Insert Excel command to delete entire row

    Excel: Delete Active Cell with option entire row.
  • If duplicate row is not found, go to the next cell in the row below.

    Excel: Go to one cell below the active cell.


Screenshot 1


Screenshot 2


Screenshot 3

When you run this task, it will sort the data in an excel sheet. Then it will loop through each cell in first column until word 'STOP' is found and will delete duplicate entries from the first column.