How to check column if it contains that data

[Sshasnk]

We have a dataset where we have three friends SLC, Kim, Tom, and Hanks. And out of these four SLC has the responsibility to get fruits. But in case if any of the other friends get the fruits then we have to remove them from 

Prd_Id	Name	Type	Price
123	SLC	Fruit	125.01
345	Kim	Vegetables	1457.00
123	Tom	Fruit	145.01
123	Hanks	Fruit	4527
456	SLC	Fruit	124.01
890	SLC	Fruit	126.01
159	Tom	Fruit	457.01
890	Kim	Fruit	523

Condition:

1. I have to check if the same product id and type is part of the sheet-> if they do I have to remove them

2. I duplicate was found then we have to select the record which has SLC

Example:

Product id -> 123 - SLC- fruit is also present for tom and hank so I have to remove them

another example: product id -> 159 - Tom - Fruit -> As SLCdoes have the same product id and type in the sheet so we have to include this

Output:

Prd_Id	Name	Type	price
123	SLC	Fruit	125.01
345	Kim	Vegetables	1457.00
456	SLC	Fruit	124.01
890	SLC	Fruit	126.01
159	Tom	Fruit	457.01

Let me know if any confusion 

[AngelosPachis]

Hey @Sshasnk ,

Assuming I understand the requirements correctly, you can do what you are looking to achieve with a summarize tool

Hope I understand correctly and this helps.

Cheers,

Angelos

[ChrisTX]

Sort by Product Id, Type, and Name: this will put Alex at the top.  You can create a separate mapping table for a custom sort order, if necessary.

Then use the Unique tool, unique values on Product Id and Fruit.  Take the output from the U anchor.  The duplicates will be in the D anchor.

Chris

[Sshasnk]

@AngelosPachis @ChrisTX  

Please don't go by alphabetical order because I have created this as sample data, In a real scenario, it is SLC instead of Alex.

Also, I cant use summarize tool because I have more than 30 fields in the dataset

[ChrisTX]

You can use Text Input to create a custom table, columns for Field Name and Sort Order, add SLC to the first column and 1 to the 2nd column.

Join from your data file to the custom table to get the Sort Order field.  Then sort by the custom sort order before using the Unique tool.  Try it.

Chris