This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Problem description & analysis:
In the Excel table below, the 1st column contains sequence numbers and the 2nd column has duplicate values.
Task: Record the value appearing for the first time in the 2nd column and replace its duplicates with the string "cont.".
Solution:
Use SPL XLL to enter the following formula:
=spl("=((d=E(?))\d.group@1(Date)).(Date=$[cont.]),d",A1:B8)
As shown in the picture below:
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.
This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Judith-Excel-Sharing | Sciencx (2024-08-01T03:45:47+00:00) #37 — Replace Duplicate Items with The Specified Text. Retrieved from https://www.scien.cx/2024/08/01/37-replace-duplicate-items-with-the-specified-text/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.