Find the desired row in similar rows

Question

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:

RowNameLocationAppleBananaCarLemonMelonYes Count1ABC Inc.EuropeYes Yes Yes32ABC Inc.Europe  YesYesYes33ABC Inc.EuropeYesYesYesYesYes54DEF Inc.AsiaYes   Yes25DEF Inc.AsiaYesYesYes Yes46XYZ Inc.Asia   Yes 17XYZ Inc.Asia  Yes  18LMN Inc.Europe  Yes  19LMN Inc.Europe Yes Yes 210LMN Inc.Europe YesYes   2

Output

RowNameLocationAppleBananaCarLemonMelonYes Count3ABC Inc.EuropeYesYesYesYesYes55DEF Inc.AsiaYesYesYes Yes46XYZ Inc.Asia   Yes 17XYZ Inc.Asia  Yes  19LMN Inc.Europe Yes Yes 210LMN Inc.Europe YesYes   2

Gawa · Answer

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.

1202337_gawa.yxmd

k3pineapple · Answer

@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

k3pineapple · Answer

@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