Remove or seperate all rows that have duplicates

Question

Hi,

Last column is like a transaction number. Looking into the first 3 rows, the old transaction, line 1, generates new lines 2 and 3. This results in 2 identical lines (1 and 2). I want to remove or separate the identical rows.

2-1-2020, AAA, 1200, 1234

2-1-2020, BBB, 600, 1234

6-4-2020, EE, 50, 2000

6-4-2020, FF, 20, 2000

The result should show only 2 lines in this example, so I can't Unique.

2-1-2020, BBB, 600, 1234

6-4-2020, FF, 20, 2000

Tried and searched for more then an hour now, so any help is so appreciated.

Thanks

grazitti_sapna · Answer

Hi @TomNL , did you try multi-row formula tool? One way of doing is by creating a flag field with formula:

if [Field1]=[Row-1:Field1] then "Match"
elseif [Field1]=[Row+1:Field1] then "Match"
else "Not Matched" endif

and then use a filter you will get your end result.

I hope this helps, kindly mark this post as solution. In case you have any query we will try to help you.

Thanks.

Remove or seperate all rows that have duplicates.yxmd

Kenda · Answer

@TomNL

Additionally, note that the unique tool will keep the first record that is a duplicate so if you want the last record for each set, I would recommend using a summarize tool instead of a unique tool. To configure the summarize tool, group by your newly created field then select the last of your original field.

Kenda · Answer

Hi @TomNL

There is one intermediate step that I would recommend before using your unique tool. You need to separate out the last piece of your string. To do this, use a formula tool and create a new field with the following expression:

REGEX_Replace([Field1], ".*\s(.*)", "$1")

This is taking your field and saying only keep the part of it that is after the last space. After this, you can use a unique tool and check this newly created field.

Hope this helps!