Rotation

[Lee733]

I need a formula or formulas to determine which part each person would receive if the idea is to give them approximately the same number of parts.

I sort the parts by quantity and give the first six people the part with the highest quantity.

I need a formula that will look at the person with the lowest total quantity and give them the next part.

The correct answer is below but it manual done. I can't figure out what formulas I could use to get the correct result.

Debbie

 

[Anthony47]

Try
in C15

=XLOOKUP(MIN(XLOOKUP(SEQUENCE(6),C$2:C14,D$2:D14,0,0,-1)),D$2:D14,C$2:C14,,0)

and in D15

=MIN(XLOOKUP(SEQUENCE(6),C$2:C14,D$2:D14,0,0,-1))+B15

These formulas works after the first 6 lines have been correctly populated

Cartel1
	A	B	C	D
1	Part	Quantity	Six Реор1е
2	181808	9380	1	9380
3	184217	8880	2	8880
4	184320	7666	3	7666
5	181703	7103	4	7103
6	184319	6727	5	6727
7	181702	6071	6	6071
8	184206	4588	6	10659
9	181807	4569	5	11296
10	184216	3895	4	10998
11	182015	2326	3	9992
12	182011	2133	2	11013
13	181809	2056	1	11436
14	181704	1615	3	11607
15	182013	1234	6	11893
16	182014	1030	4	12028
17	181701	890	2	11903
18	181805	879	5	12175
19	182010	838	1	12274
20	184318	781	3	12388
21	182012	177	6	12070
22	TOTALS	72838		72838
23
Foglio2

Cell Formulas
Range	Formula
C8:C21	C8	=XLOOKUP(MIN(XLOOKUP(SEQUENCE(6),C$2:C7,D$2:D7,0,0,-1)),D$2:D7,C$2:C7,,0)
D8:D21	D8	=MIN(XLOOKUP(SEQUENCE(6),C$2:C7,D$2:D7,0,0,-1))+B8

 

[Lee733]

Thank you so much!

 

[Anthony47]

Does it fix the problem?

 

[Lee733]

I entered your formula in D9 and it is adding 6071+4569 = 10,640 because it is taking person #6 again. I am doing something wrong.

 

[Anthony47]

The formulas I gave are for line 15
Then you may copy them backword (up to line 8)
Or you can copy the formula for line 9 from the XL2BB minisheet I attached

 

[Anthony47]

Which formula did you enter in C9 and D9?

 

[Lee733]

=XLOOKUP(MIN(XLOOKUP(SEQUENCE(6),C$2:C14,D$2:D14,0,0,-1)),D$2:D14,C$2:C14,,0)

This is the formula that I used in C15 and it give the result as 3.

 

[Anthony47]

Can you provide al least an image of your data, with coordinates?

 

[Lee733]

C9 - =XLOOKUP(MIN(XLOOKUP(SEQUENCE(6),C$2:C8,D$2:D8,0,0,-1)),D$2:D8,C$2:C8,,0)
D9 - =MIN(XLOOKUP(SEQUENCE(6),C$2:C8,D$2:D8,0,0-1))+B9