Delete Duplicate Excel Rows
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.
Note: Save the xls in C: drive, the .atmx file in My Documents -> Automation Anywhere -> Automation Anywhere -> My Tasks
- 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.
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.