Search code examples
vbaexcel-formulaexcel-2010

Stuck in one condition in excel

I have one column in excel "Variants" in sheet1 which contain no. of parts in A column. These parts are remaining parts which are left to allocate to analyst.

enter image description here

another sheet2 has two column "Analyst" ,"count". Analyst contain analyst name or my team name and count contain number of different parts they have already allocated. like below

enter image description here

Now I want to allocate remaining parts which is in Variant column to my analyst in such a manner that there count will be equal. below is my expected output

enter image description here

See I allocate only one part to jawed in sheet1 because it has already count 2 and remaining analyst got 3 parts to match equal count

Analyst New count jawed 3 rohan 3 moti 3 sohan 3

I tried below steps Step-1 In Sheet2, in cell C2, enter the formula =MAX(0, B2-COUNTIF(Sheet1!A:A, A2)). Drag this formula down for all analysts.

step-2

In Sheet1, in cell B2 (next to the first variant) entered the below formula =IFERROR(INDEX(Sheet2!A:A,MATCH(MIN(Sheet2!$C$2:$C$5),Sheet2!$C$2:$C$5,0)),"")

step-3 In Sheet2, in cell B2, enter the formula =B2+COUNTIF(Sheet1!B:B, A2). Drag this formula down for all analysts.

But it is not giving me what i am expecting.

Solution

  • You can use REDUCE to calculate which name to use each time.

    =DROP(REDUCE("",A2:A13,LAMBDA(x,y,LET(z,BYROW(C2:C5,LAMBDA(v,SUM(N(v=x)))),VSTACK(x,TAKE(TOCOL(IFS((D2:D5+z)=MIN((D2:D5+z)),C2:C5),2),1))))),1)

    It checks the count of the name of the Analyst in your range and in the results of the formula itself row by row.

    A2:A13 is the range of the values you need to assign. The range is not actually used in the formula, but the number of cells of the range is used to loop the REDUCE lambda, where x will be the repeated result of the formula being stacked.

    So in the first calculation it checks for the count of the analyst names C2:C5 being used in the start value (nothing / ""). Which results in 0 for each name of course.

    It then adds up this count to the current count D2:D5. The first value to equal the smallest value of the array is used to return the associated name. This is the result of the first calculation.

    Then it's looped back to the second calculation, which checks for the count of the analyst names being used in the previously stacked calculation result(s). No it will return +1 for the previous calculated name. When checking the smallest count of the analyst names; this count is added to the existing values and the first name which is used the least gets picked, etc, etc.