Extract values from rows and columns

Hi all,

Sorry if this question asked earlier.  I searched but could not find. I am not a programming person.  Please help me.  Here is my query:

Lets say there are 5 groups i.e. A,B,C,D and E and every groups have 7 packages.

Say 10 firms are bidding for those 5 groups with all 7 packages.  Every firm will be allotted some scores on the basis of some criteria.

Highest score firm will get the package of a particular group i.e. A firm can get maximum of 1 package in each group. That firm cannot get package in the same group.  If same firm get the highest mark in 2 or more packages in a same group then the award of project shall be done on basis of least cost of the firm.  Let say firm 1 get highest mark in 3 packages i.e. 80, 81 and 82.  The firm will get package in which it scored 80.

I am attaching a workbook in which I have mentioned 3 packages of the same group.  I need firm name and score of winning firm in different worksheet keeping in mind above conditions.

Thanks in advance.

Sunil

Your data is not clear. What is A, B, C? If they are the firms, none of the examples illuminate the cases you mention here.

A,B,C, D are groups.  For the time being you can take only Group A.  Every group has packages i.e. package 1, Package 2, Package 3, etc.  Any firm can apply for any or all packages in the group.

If you see in worksheet, on the top Row, I have mentioned Group then Package.  Name columns is used for Firms name.

I hope it clarifies you doubt.

Thanks

Sorry for the inconvenience.

A,B,C, D are groups.  For the time being you can take only Group A.  Every group has packages i.e. package 1, Package 2, Package 3, etc.  Any firm can apply for any or all packages in the group.

If you see in worksheet, on the top Row, I have mentioned Group then Package.  Name column is used for Firms name. Sorry it was named as group name.  You can change it as per your convenience.

I hope it clarifies your doubt.

Thanks

Your approach would only work if all packages were approximately the same (money) value.

Attached is my manual sorting approach using the only best scores that include all packages and all suppliers.
Goal is to award all four packages to separate vendors that results in best total score.

Dear Jim,

Thanks for your reply.  I appreciate your help.  I have gone the attachment which you sent.  Actually the sheet I attached is the example actual work is much more than this.  Firms can be more than 20.  Packages can be more than 10 and groups can be more 4.  As you can understand that manual approach will not work here.  I need an automated solution in Excel so that if we add group and packages in the group, it should show us result in different sheet.

Regards,