Data sort / group under some conditions

[LouisLouis]

Welcome,

I've got data as shown. My role is to sort them / group them under some conditions simultaneously:

condition no 1: for one group of 'H1' maximum 'PCS' sum is 25 / for H2 is 9 (condition no2 must be kept)
condition no 2: sum of val multiplied by 'PCS' is not greather than 9 for records with H1 and not greather than 8 for 'H2',
condition no 4: 'H1' nad 'H2' could be mixed in one grup but sum of val multiplied by 'PCS' must be not greather than 8 then,
condition no 5: if 'H1' and 'H2' are mixed in one group maximum 'PCS' sum for H1 is 12 and for H2 is 6, when imposible - do not
mix in one group



[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl66, width: 64"]Article[/TD]
[TD="class: xl66, width: 64"]val[/TD]
[TD="class: xl66, width: 64"]PCS[/TD]
[TD="class: xl66, width: 64"]val1[/TD]
[/TR]
[TR]
[TD="class: xl66"]No1[/TD]
[TD="class: xl66"]0,1[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]H1[/TD]
[/TR]
[TR]
[TD="class: xl66"]No1[/TD]
[TD="class: xl66"]0,7[/TD]
[TD="class: xl66"]6[/TD]
[TD="class: xl66"]H2[/TD]
[/TR]
[TR]
[TD="class: xl66"]No1[/TD]
[TD="class: xl66"]0,3[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]H1[/TD]
[/TR]
[TR]
[TD="class: xl66"]No1[/TD]
[TD="class: xl66"]0,7[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]H1[/TD]
[/TR]
[TR]
[TD="class: xl66"]No1[/TD]
[TD="class: xl66"]0,2[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]H1[/TD]
[/TR]
[TR]
[TD="class: xl66"]No1[/TD]
[TD="class: xl66"]1,5[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]H2[/TD]
[/TR]
[TR]
[TD="class: xl66"]No2[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]6[/TD]
[TD="class: xl66"]H2[/TD]
[/TR]
[TR]
[TD="class: xl66"]No2[/TD]
[TD="class: xl66"]0,1[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]H1[/TD]
[/TR]
[TR]
[TD="class: xl66"]No2[/TD]
[TD="class: xl66"]0,8[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]H1[/TD]
[/TR]
[TR]
[TD="class: xl66"]No4[/TD]
[TD="class: xl66"]0,4[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]H2[/TD]
[/TR]
[TR]
[TD="class: xl66"]No5[/TD]
[TD="class: xl66"]0,7[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]H1[/TD]
[/TR]
[TR]
[TD="class: xl66"]No6[/TD]
[TD="class: xl66"]0,3[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]H2[/TD]
[/TR]
[TR]
[TD="class: xl66"]No6[/TD]
[TD="class: xl66"]0,3[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]H2[/TD]
[/TR]
[TR]
[TD="class: xl66"]No6[/TD]
[TD="class: xl66"]0,1[/TD]
[TD="class: xl66"]11[/TD]
[TD="class: xl66"]H1[/TD]
[/TR]
[TR]
[TD="class: xl66"]No6[/TD]
[TD="class: xl66"]0,6[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]H2[/TD]
[/TR]
</tbody>[/TABLE]



Effect of doing it "on foot" (beneath)


[TABLE="width: 294"]
<tbody>[TR]
[TD="colspan: 4"]GROUP 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Article No[/TD]
[TD]val[/TD]
[TD]PCS[/TD]
[TD]val1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No1[/TD]
[TD]0,1[/TD]
[TD]1[/TD]
[TD]H1[/TD]
[TD]0,1[/TD]
[/TR]
[TR]
[TD]No1[/TD]
[TD]0,3[/TD]
[TD]10[/TD]
[TD]H1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]No1[/TD]
[TD]0,7[/TD]
[TD]4[/TD]
[TD]H1[/TD]
[TD]2,8[/TD]
[/TR]
[TR]
[TD]No1[/TD]
[TD]0,2[/TD]
[TD]9[/TD]
[TD]H1[/TD]
[TD]1,8[/TD]
[/TR]
[TR]
[TD]No2[/TD]
[TD]0,1[/TD]
[TD]1[/TD]
[TD]H1[/TD]
[TD]0,1[/TD]
[/TR]
[TR]
[TD="colspan: 2"]TOTAL[/TD]
[TD]25[/TD]
[TD][/TD]
[TD]7,8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]GROUP 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Article No[/TD]
[TD]val[/TD]
[TD]PCS[/TD]
[TD]val1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No6[/TD]
[TD]0,1[/TD]
[TD]1[/TD]
[TD]H1[/TD]
[TD]0,1[/TD]
[/TR]
[TR]
[TD]No2[/TD]
[TD]0,8[/TD]
[TD]5[/TD]
[TD]H1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]No5[/TD]
[TD]0,7[/TD]
[TD]5[/TD]
[TD]H1[/TD]
[TD]3,5[/TD]
[/TR]
[TR]
[TD]No6[/TD]
[TD]0,1[/TD]
[TD]10[/TD]
[TD]H1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="colspan: 2"]TOTAL[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]8,6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]GROUP 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Article No[/TD]
[TD]val[/TD]
[TD]PCS[/TD]
[TD]val1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No1[/TD]
[TD]0,7[/TD]
[TD]6[/TD]
[TD]H2[/TD]
[TD]4,2[/TD]
[/TR]
[TR]
[TD]No1[/TD]
[TD]1,5[/TD]
[TD]2[/TD]
[TD]H2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]No6[/TD]
[TD]0,6[/TD]
[TD]1[/TD]
[TD]H2[/TD]
[TD]0,6[/TD]
[/TR]
[TR]
[TD="colspan: 2"]TOTAL[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]7,8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]GROUP 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Article No[/TD]
[TD]val[/TD]
[TD]PCS[/TD]
[TD]val1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]H2[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD="colspan: 2"]TOTAL[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]GROUP 5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Article No[/TD]
[TD]val[/TD]
[TD]PCS[/TD]
[TD]val1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]H2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]No4[/TD]
[TD]0,4[/TD]
[TD]3[/TD]
[TD]H2[/TD]
[TD]1,2[/TD]
[/TR]
[TR]
[TD]No6[/TD]
[TD]0,3[/TD]
[TD]1[/TD]
[TD]H2[/TD]
[TD]0,3[/TD]
[/TR]
[TR]
[TD]No6[/TD]
[TD]0,3[/TD]
[TD]2[/TD]
[TD]H2[/TD]
[TD]0,6[/TD]
[/TR]
[TR]
[TD="colspan: 2"]TOTAL[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]6,1[/TD]
[/TR]
</tbody>[/TABLE]


Of course target is to make as low number of groups as possible but keeping A/M conditions.


If anyone could - kindly please help. Best way with VBA and custom functions.


Thanks.
LouisLouis