2.2.3.44 wdeldup


Menu Information

Worksheet: Remove/Combine Duplicated Rows

Brief Information

Remove or combine worksheet rows based on duplicates in a reference column

Additional Information

Minimum Origin Version Required: 2015 SR0

Command Line Usage

wdeldup irng:=col(a) sensitive:=1;

Variables

Display
Name
Variable
Name
I/O
and
Type
Default
Value
Description
Reference Column irng

Input

Range

<active>
Specify the column which needs to be deleted duplicated rows.
Merge Duplications by keep1st

Input

int

1
Specify how to reduce worksheet with duplicated rows.

Option list:

  • removeAll{0}:Remove All
    Remove all duplicated rows including first duplicated row
  • keep1st{1}:Keep First Row
    Keep first duplicated row and remove all other duplicated rows
  • keepLast{2}:Keep Last Row
    Keep last duplicated row and remove all other duplicated rows
  • average{3}:Average
    Reduce other columns in worksheet by their average value according to the duplicated rows in Reference Column
  • min{4}:Min
    Reduce other columns in worksheet by the minimum value according to the duplicated rows in Reference Column
  • max{5}:Max
    Reduce other columns in worksheet by the maximum value according to the duplicated rows in Reference Column
  • sum{6}:Sum
    Reduce other columns in worksheet by their sum according to the duplicated rows in Reference Column
  • sd{7}:SD
    Reduce other columns in worksheet by their standard deviation according to the duplicated rows in Reference Column
Case Sensitive sensitive

Input

int

0
Specify whether to be case sensitive in comparing two strings.
undo

Input

int

1
Specify whether to allow the "undo" ability. If you have enabled it, you can select Edit: Undo to erase the last operation to the worksheet.

Note:It is designed for the script usage, not availble from dialog

Output Worksheet ow

Output

Worksheet

<input>
Specify the output worksheet.

See the syntax here.

Output Counts counts

Input

int

0
Specify whether to count and output the number of duplicate rows.

Description

This X-Function can remove/combine duplicated rows in a worksheet. It can be accessed from menu, command window.

Examples

Suppose a user has data as following and want to remove the duplicated strings in column A

A B
ABC 1
abc 2
ABC 3
def 4
ABC 5
abc 6
ABC 7
  1. Hightlight the 1st column and select Worksheet: Remove/Combine Duplicated Rows from menu to open the dialog. Select the Case Sensitive check box and Output Counts check box
  2. Click OK to delete the duplicated rows. The result should look like
A B Count
ABC 1 4
abc 2 2
def 4 1

Related X-Functions


Keywords:reduce, reduction