Hello there

My requirement/situation is as follows;

The table given below has a few data points - AA contains specific groups and sub groups concatenated; Rank colm refers to the corresponding ranking for each of these sub groups. Current CL is the current class of these individual records. however, there is a reqd ratio on the distribution of these Class levels in a group. the reqd ratio is available in the order (CL1:CL2:CL3). Now what i need is the recommended CL mapping based on th reqd ratio

the logic to be applied to derive at the recommended CL is

1. in the particular sub group the most min value needs to be marked CL1, for eg in the first group abc- we have sub group abc1.1, the first record is already CL1, hnce i need 2 more CL1's there. So recd in the row 2 and recd in row 3 should be mapped as CL1.
2. If the count of CL1 does not match the reqd ratio count in the particular sub group, i should move to the next subgroup under the group since the ratio applies to the overall group.
3. Similar logic to be applied to CL2/ CL3 also in order of their ranking corresponding to the sub group, and the ideal recommended CL mapping to be given in the recommended CL colm.

I have tried multiple ifs/array and min. However am missing something somewhere.

its pretty urgent and I would be sooo thankful for any help!!


AA Rank Current CL Reqd Ratio (CL1:CL2:CL3) Recommended CL
abc1.1 1 CL1 3 : 3 : 1 CL1
abc1.1 2 CL2 3 : 3 : 1
abc1.1 3 CL2 3 : 3 : 1
abc1.1 4 CL3 3 : 3 : 1
abc1.1 4 CL2 3 : 3 : 1
abc1.2 1 CL3 3 : 3 : 1
abc2.1 1 CL1 3 : 3 : 1
bcd2.1 1 CL2 1 : 1 : 0
bcd2.1 2 CL2 1 : 1 : 0
dce1.1 1 CL3 2 : 2 : 1
dce1.2 1 CL2 2 : 2 : 1
dce2.1 1 CL1 2 : 2 : 1
dce2.2 1 CL2 2 : 2 : 1
dce2.2 2 CL1 2 : 2 : 1