Keep only one record

[john_armentrout]

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

[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 · Accepted solution]

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:

[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