Find Duplicate Values

 

Example

Part of the data in Excel file Book1.xlsx is as follows:

EID

NAME

dup

1

Rebecca


2

Ashley


3

Rachel


4

Emily


5

Ashley



If there is a duplicate name, fill in dup with 1, otherwise fill with 2. The result is as follows:

EID

NAME

dup

1

Rebecca

2

2

Ashley

1

3

Rachel

1

4

Emily

2

5

Ashley

1

Write SPL script:


A

1

=file("Book1.xlsx").xlsimport@t()

2

=A1.run(dup=if(A1.(NAME).count(~==NAME)>1:1;2))

A1 Read the Excel data

A2 Traverse each data, if there is a duplicate NAME value, dup will be 1, otherwise it will be 2