DEV Community

Cover image for #37 — Replace Duplicate Items with The Specified Text
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

#37 — Replace Duplicate Items with The Specified Text

Problem description & analysis:

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

original table

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

desired table

Solution:

Use SPL XLL to enter the following formula:

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

As shown in the picture below:

result table with code entered
Explanation:

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.

Top comments (2)

Collapse
 
judith677 profile image
Judith-Excel-Sharing

Please feel free to download SPL XLL and boost your Excel productivity ⬇️

🔎SPL download address: scudata.com/download-Desktop

💡Plugin Installation Method: c.scudata.com/article/1652061135502

🔎References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524

💡SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...

Some comments may only be visible to logged-in visitors. Sign in to view all comments.