Remove or seperate all rows that have duplicates

[TomNL]

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, AAA, 1200, 1234

2-1-2020, BBB, 600, 1234

6-4-2020, EE, 50, 2000

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

[Kenda]

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! 

[Kenda]

@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.

[grazitti_sapna]

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.