Hi - I have the following situation - I have some duplicate rows (for example, the first two in this sample set), I want to keep only the row where the PartTypeValue is "MPS": NameSiteIDPartTypeValueMin_Warehouse00-011085300MPSWH300000-011085300MakeWH300020402100MakeUSA10220402110MakeWH110120402112MPSWH100820402112MakeWH100820402113MakeUSA20420402114MakeWH100920402120MakeUSA10120402200MPSUSA20120402300MakeUSA30120402400MakeIRL101 What is needed is a single record (unique to Name and SiteID) - keeping the MPS record when applicable: NameSiteIDPartTypeValueMin_Warehouse00-011085300MPSWH300020402100MakeUSA10220402110MakeWH110120402112MPSWH100820402113MakeUSA20420402114MakeWH100920402120MakeUSA10120402200MPSUSA20120402300MakeUSA30120402400MakeIRL101 Not sure how to "remove" the second, non-MPS record in those instances were I have two records for a unique Name-SiteID combination. thanks - john

Keep only one record

Hi -

I have the following situation - I have some duplicate rows (for example, the first two in this sample set), I want to keep only the row where the PartTypeValue is "MPS":

Name	SiteID	PartTypeValue	Min_Warehouse
00-011085	300	MPS	WH3000
00-011085	300	Make	WH3000
20402	100	Make	USA102
20402	110	Make	WH1101
20402	112	MPS	WH1008
20402	112	Make	WH1008
20402	113	Make	USA204
20402	114	Make	WH1009
20402	120	Make	USA101
20402	200	MPS	USA201
20402	300	Make	USA301
20402	400	Make	IRL101

What is needed is a single record (unique to Name and SiteID) - keeping the MPS record when applicable:

Name	SiteID	PartTypeValue	Min_Warehouse
00-011085	300	MPS	WH3000
20402	100	Make	USA102
20402	110	Make	WH1101
20402	112	MPS	WH1008
20402	113	Make	USA204
20402	114	Make	WH1009
20402	120	Make	USA101
20402	200	MPS	USA201
20402	300	Make	USA301
20402	400	Make	IRL101

Not sure how to "remove" the second, non-MPS record in those instances were I have two records for a unique Name-SiteID combination.

thanks - john

Output

Parse

Accepted answers

Chantelb

Hi @john_armentrout ,

If there are other possible values for PartTypeValue you could use a join and filter in addition to the unique tool to make sure you always get MPS:

RemoveDuplicates.yxmd

All comments

Christine_Assaad

Hello @john_armentrout

Will it always be MPS or Make? If yes, you can sort the data by Name, then by PartTypeValue in a descending order then add a Unique tool to remove duplicates based on Name and Site ID like below.

john_armentrout

it will always be MPS, if there is an MPS row - otherwise, it would be Make (there is another value, "Buy", that is treated the same way - MPS overrides both, otherwise whatever the other value is). Let me try what you suggested...

Chantelb

Hi @john_armentrout ,

If there are other possible values for PartTypeValue you could use a join and filter in addition to the unique tool to make sure you always get MPS:

RemoveDuplicates.yxmd

john_armentrout

as expected, the approach works for MPS and Make - but not for MPS and Buy. In my 15000 rows, I have examples of "duplicates" with MPS and Make, and MPS and Buy. always need MPS to win...

john_armentrout

@Chantelb Thanks - that seemed to do it. I'm validating, but thanks much!

john_armentrout

@Chantelb - validated,thanks again

Quick Links

This months top contributors

FláviaB 262

caltang 177

DanM 167

IraWatt 157

gawa 157