Find the desired row in similar rows

[k3pineapple]

Hi all

Seeking your kind help find desired row here. I am struggling to filter the desired row out.

Red color rows required to be removed, not sure whether is doable or not. 

if not, can do row 1-5 first. Much appreciated.

Input:

Row	Name	Location	Apple	Banana	Car	Lemon	Melon	Yes Count
1	ABC Inc.	Europe	Yes		Yes		Yes	3
2	ABC Inc.	Europe			Yes	Yes	Yes	3
3	ABC Inc.	Europe	Yes	Yes	Yes	Yes	Yes	5
4	DEF Inc.	Asia	Yes				Yes	2
5	DEF Inc.	Asia	Yes	Yes	Yes		Yes	4
6	XYZ Inc.	Asia				Yes		1
7	XYZ Inc.	Asia			Yes			1
8	LMN Inc.	Europe			Yes			1
9	LMN Inc.	Europe		Yes		Yes		2
10	LMN Inc.	Europe		Yes	Yes			2

Output

Row	Name	Location	Apple	Banana	Car	Lemon	Melon	Yes Count
3	ABC Inc.	Europe	Yes	Yes	Yes	Yes	Yes	5
5	DEF Inc.	Asia	Yes	Yes	Yes		Yes	4
6	XYZ Inc.	Asia				Yes		1
7	XYZ Inc.	Asia			Yes			1
9	LMN Inc.	Europe		Yes		Yes		2
10	LMN Inc.	Europe		Yes	Yes			2

[caltang]

What is the logic in removing them? Or do you just want to remove them just because?

[gawa]

@k3pineapple 

I imagine that you have excel data, and some cells are highlighted in red color. You want to filter the rows having at least on red cell.

Unfortunately Alteryx cannot recognize input data format, so cannot distinguish which rows should be filtered.

As an advanced technique, you can read excel file as a zip file by Alteryx, and extract format information inside of Excel file somehow, and you can know which row should be filtered. But this is quite difficult.

[k3pineapple]

@caltang the main logic is to get row that has most Yes count row

sub - logic : if there is unique value in each row than output both rows

[k3pineapple]

@gawa thx , the logic is to get row which has most # of Yes count. the sub-logic (optional) if both rows have unique value of Yes then output both rows. hope this help

[gawa]

hi @k3pineapple 

I got it, then it is super easy.

First, get max YES count for each companies by Summarize tool. Next, Join input data and summarized data, then you can get the desired output.

For your reference, to get max record of group, combo of "Sort and Sample" can be also used, however this does not work when data is having multiple record with the same rank like your case.