RaqForum 41 No.
146 View  • 
     ...

Replace duplicate items with the specified text

 

In the Excel table below, the 1st column contains sequence numbers and the 2nd column has duplicate values.

A

B

1

Row No.

Date

2

1

01.01.2023

3

2

01.01.2023

4

3

01.01.2023

5

4

02.01.2023

6

5

01.01.2023

7

6

03.01.2023

8

7

02.01.2023

Task: Record the value appearing for the first time in the 2nd column and replace its duplicates with the string "cont.".

D

E

1

Row No.

Date

2

1

01.01.2023

3

2

cont.

4

3

cont.

5

4

02.01.2023

6

5

cont.

7

6

03.01.2023

8

7

cont.

Use SPL XLL to enter the following formula:

=spl("=((d=E(?))\d.group@1(Date)).(Date=$[cont.]),d",A1:B8)

Picture1png

E() function converts the data range to a two-dimensional table. group@1 groups rows and retrieves the 1st row of each group. The operator \ finds the difference of two sets.

Source:https://stackoverflow.com/questions/78159410/select-and-replace-duplicates


 
 •  146 View  •