Sorting Apples Questions - Using Macros

[lotien15]

Hi,

Im trying to see if its possible to build a macro with the scenario mentioned below:

We have 3 people (Person A, B, and C). All have different set of apples that are each uniquely barcoded that need to be evenly distributed into 6 baskets. When it comes to uneven numbers divisible by 6, they need to be added back into one of the other baskets (in others words, would restart the count OR basket 1 & 2 would get two apples from Person B); can't have part of an apple.

Person A = 6 Apples
Person B = 8 Apples
Person C = 24 Apples

The goal would be to filter at the column for basket '1' and I would get all of the listed assigned apples from each person.

 

[BSALV]

Map2 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		count	Round 6		basket	1	2	3	4	5	0		remaining
2	a	6	6		a	1	2	3	4	5	6		empty
3	b	8	6		b	1	2	3	4	5	6		7,8
4	c	24	24		c	1,7,13,19	2,8,14,20	3,9,15,21	4,10,16,22	5,11,17,23	6,12,18,24		empty
Blad1

Cell Formulas
Range	Formula
E2:E4	E2	=+A2
F2:K4	F2	=TEXTJOIN(",",1,FILTER(SEQUENCE($C2),MOD(SEQUENCE($C2),6)=F$1))
C2:C4	C2	=FLOOR.MATH(B2,6)
M2:M4	M2	=IFERROR(TEXTJOIN(",",1,FILTER(SEQUENCE(B2),SEQUENCE(B2)>C2)),"empty")

 

[lotien15]

Map2 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		count	Round 6		basket	1	2	3	4	5	0		remaining
2	a	6	6		a	1	2	3	4	5	6		empty
3	b	8	6		b	1	2	3	4	5	6		7,8
4	c	24	24		c	1,7,13,19	2,8,14,20	3,9,15,21	4,10,16,22	5,11,17,23	6,12,18,24		empty
Blad1

Cell Formulas
Range	Formula
E2:E4	E2	=+A2
F2:K4	F2	=TEXTJOIN(",",1,FILTER(SEQUENCE($C2),MOD(SEQUENCE($C2),6)=F$1))
C2:C4	C2	=FLOOR.MATH(B2,6)
M2:M4	M2	=IFERROR(TEXTJOIN(",",1,FILTER(SEQUENCE(B2),SEQUENCE(B2)>C2)),"empty")

For Person B, you are still left with apple 7 and 8. Is there a way to distribute apple 7 into basket 1 and apple 8 into basket 2? Basically, to continue the distribution till it runs out of apples per person; then continues for distribution with the next person starting with basket 1 and so forth.

 

[Fluff]

Just change the $C2 in the formula to $B2 & then drag down & across.

=TEXTJOIN(",",1,FILTER(SEQUENCE($B2),MOD(SEQUENCE($B2),6)=F$1))

 

[lotien15]

Same questions but different format. How would I continuously number the apples (rows) but restart the count with each new type of apple like so?

 

[Fluff]

As this is a totally different question, you need to start a new thread.

Did the change I suggested work for you?

 

[lotien15]

Yes it did. Thank you!

I thought that the updated situation was a similar idea in principle but shows you how little I know -- Will post a new thread for it.

 

[Fluff]

Glad we could help & thanks for the feedback.