One click Excel integration. No programming required.

  • Home
  • Sort and Delete Duplicate Rows

Delete Duplicate Excel Rows - T29

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

Solutions

  1. Define Variables
    • Go to Variable Manager and create variables "C1", "C2", "N1" and "N2"
  2. Create new task
    • Click on New->Task Editor.
  3. Open Excel sheet
    • From Application Category->Excel, Select 'Open Spreadsheet' option.
  4. 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"
  5. Go through all the cells until word 'STOP' found
    • Insert Loop for variable "Clipboard" not equal to "STOP".
  6. Retrieve value of 1st cell
    • Insert Excel command to get the value of the first cell
  7. 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$
  8. 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
  9. 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.

T29-1

Screenshot 1

 

T29-2

Screenshot 2

 

T29-3

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.

Go be great.

Automation Anywhere empowers people, whose ideas, effort and focus make the companies they work for, great. Delivering intelligent automation technology for business & IT processes so you and your company can always be depended on.