Trips selector

Hello everybody,

I am looking for a formula which is able to select number of trips with highest priorities.

1 T P F
2 M J K
3 K Q R
4 D G H
5 T G H
6 A Q R
7 M Q R
8 E Q R
9 L Q R
10 R E Q
11 L A K
12 B H C
13 N M H
14 O E Q
15 H E Q

This table describes that the first trip #1 goes from point "T" to point "P" to point "F". The second trip #2 goes from point "M" to point "J" to point "K". Etc.

The result should be looking like shown below:
1 T P F
2 M J K
4 D G H
6 A Q R

The trip number 3 is not possible to choose as a proper trip because the point "K" is already chosen in the higher priority trip #2.

Is it possible to somehow automate those results above?

Thank you.

=--(SUM(COUNTIF($B$1:D1,B2:D2))=0) as array formula

Thank you for your reply.

But, trip #6 is supposed to be also the right solution! Your excel file does have this solution included.

6 A Q R

...bu Q and R are already chosen ????

I am sorry, let me clarify the situation.

The point K is taken within the trip #2, it means that formula should skip trip #3 and not consider point Q and R as chosen. It is needed to consider all three point within each trip number.

Thanks

in this case UDF

Please Login or Register  to view this content.

Thank you, it works perfectly!

the same udf as array formula
more effitient for large data volumes

Please Login or Register  to view this content.

It is strange
"your" files are slow
my are Dodge Vipers

Thank you. Do you know what could be a reason?