Loader/Pallet list: How to organise quantities to a particular location.

[Mars reject]

Hi,
I have been stuck for quite some time trying to figure this out. Essentially there is a pallet which needs to be loaded up with metal blanks to be machined by automation. The pallet has 4 rows and 24 spaces in each row. The first will start on row 1, group 1. If there are 4 types of this tool needed to be made then the next set will start at row 1, group 5 etc.

The part I'm struggling with is setting this up so that by only entering the quantity of the tools it adds the locations of them automatically. I'm particularly struggling with the last space so once it has been used the row is now 2 in the table and the rest carry over. So for example if there are 4 blanks which start at row 1, group 23 then it carries over the pair so rather than a new pair starting at row 2 group 1, it will instead start at row 2, group 3.

I hope I have explained this ok. I have attached a screenshot of the spreadsheet which I'd like to the locations update automatically rather than typing them in. In red are the rows and groups (locations and the part I'm trying to get to update automatically) and in blue are the quantities (which the user inputs, and the row/group columns update accordingly).

Thank you in advance for any help on this!

 

[JGordon11]

No screenshot was attached so not entirely sure what you're going for. Maybe:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Tool	Qty	Running Total			Slot1	Slot2	Slot3	Slot4	Slot5	Slot6	Slot7	Slot8	Slot9	Slot10	Slot11	Slot12	Slot13	Slot14	Slot15	Slot16	Slot17	Slot18	Slot19	Slot20	Slot21	Slot22	Slot23	Slot24
2	Tool1	4	4		Row1	Tool1	Tool1	Tool1	Tool1	Tool2	Tool2	Tool2	Tool2	Tool2	Tool3	Tool3	Tool3	Tool3	Tool3	Tool3	Tool4	Tool4	Tool4	Tool4	Tool4	Tool4	Tool4	Tool5	Tool5
3	Tool2	5	9		Row2	Tool5	Tool6	Tool6	Tool6	Tool6	Tool7	Tool7	Tool7	Tool7	Tool7	Tool8	Tool8	Tool8	Tool8	Tool8	Tool8	Tool9	Tool9	Tool10	Tool10	Tool10	Tool11	Tool11	Tool11
4	Tool3	6	15		Row3	Tool11	Tool12	Tool12	Tool12	Tool12	Tool12	Tool13	Tool13	Tool13	Tool13	Tool13	Tool13	Tool14	Tool14	Tool15	Tool15	Tool15	Tool16	Tool16	Tool16	Tool16	Tool17	Tool17	Tool17
5	Tool4	7	22		Row4	Tool17	Tool17	Tool18	Tool18	Tool18	Tool18	Tool18	Tool18	Tool19	Tool19	Tool19	Tool19	Tool19	Tool19	Tool19	Tool20	Tool20	Tool20	Tool20	Tool20	Tool20	Tool20
6	Tool5	3	25
7	Tool6	4	29
8	Tool7	5	34
9	Tool8	6	40
10	Tool9	2	42
11	Tool10	3	45
12	Tool11	4	49
13	Tool12	5	54
14	Tool13	6	60
15	Tool14	2	62
16	Tool15	3	65
17	Tool16	4	69
18	Tool17	5	74
19	Tool18	6	80
20	Tool19	7	87
21	Tool20	7	94
22	Tool21	9	103
23	Tool22	10	113
24	Tool23	11	124
25
Sheet3

Cell Formulas
Range	Formula
F2:AC5	F2	=LET(m,XLOOKUP(24*(ROW(F2:AC5)-2)+COLUMN(F2:AC5)-5,C2:C24,A2:A24,,1),IF(m=XLOOKUP(97,C2:C24,A2:A24,,1), "",m))
C2	C2	=B2
C3:C24	C3	=B3+C2
Dynamic array formulas.

 

[Mars reject]

No screenshot was attached so not entirely sure what you're going for. Maybe:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Tool	Qty	Running Total			Slot1	Slot2	Slot3	Slot4	Slot5	Slot6	Slot7	Slot8	Slot9	Slot10	Slot11	Slot12	Slot13	Slot14	Slot15	Slot16	Slot17	Slot18	Slot19	Slot20	Slot21	Slot22	Slot23	Slot24
2	Tool1	4	4		Row1	Tool1	Tool1	Tool1	Tool1	Tool2	Tool2	Tool2	Tool2	Tool2	Tool3	Tool3	Tool3	Tool3	Tool3	Tool3	Tool4	Tool4	Tool4	Tool4	Tool4	Tool4	Tool4	Tool5	Tool5
3	Tool2	5	9		Row2	Tool5	Tool6	Tool6	Tool6	Tool6	Tool7	Tool7	Tool7	Tool7	Tool7	Tool8	Tool8	Tool8	Tool8	Tool8	Tool8	Tool9	Tool9	Tool10	Tool10	Tool10	Tool11	Tool11	Tool11
4	Tool3	6	15		Row3	Tool11	Tool12	Tool12	Tool12	Tool12	Tool12	Tool13	Tool13	Tool13	Tool13	Tool13	Tool13	Tool14	Tool14	Tool15	Tool15	Tool15	Tool16	Tool16	Tool16	Tool16	Tool17	Tool17	Tool17
5	Tool4	7	22		Row4	Tool17	Tool17	Tool18	Tool18	Tool18	Tool18	Tool18	Tool18	Tool19	Tool19	Tool19	Tool19	Tool19	Tool19	Tool19	Tool20	Tool20	Tool20	Tool20	Tool20	Tool20	Tool20
6	Tool5	3	25
7	Tool6	4	29
8	Tool7	5	34
9	Tool8	6	40
10	Tool9	2	42
11	Tool10	3	45
12	Tool11	4	49
13	Tool12	5	54
14	Tool13	6	60
15	Tool14	2	62
16	Tool15	3	65
17	Tool16	4	69
18	Tool17	5	74
19	Tool18	6	80
20	Tool19	7	87
21	Tool20	7	94
22	Tool21	9	103
23	Tool22	10	113
24	Tool23	11	124
25
Sheet3

Cell Formulas
Range	Formula
F2:AC5	F2	=LET(m,XLOOKUP(24*(ROW(F2:AC5)-2)+COLUMN(F2:AC5)-5,C2:C24,A2:A24,,1),IF(m=XLOOKUP(97,C2:C24,A2:A24,,1), "",m))
C2	C2	=B2
C3:C24	C3	=B3+C2
Dynamic array formulas.

Hi thank you for replying. I'll try uploading the screenshot again, I am uploading from mobile so that could be why. I had a look into the XLOOKUP and YLOOKUP formulas but wasn't entirely sure how they worked. Would you be able to explain them? Never used them before.

No screenshot was attached so not entirely sure what you're going for. Maybe:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Tool	Qty	Running Total			Slot1	Slot2	Slot3	Slot4	Slot5	Slot6	Slot7	Slot8	Slot9	Slot10	Slot11	Slot12	Slot13	Slot14	Slot15	Slot16	Slot17	Slot18	Slot19	Slot20	Slot21	Slot22	Slot23	Slot24
2	Tool1	4	4		Row1	Tool1	Tool1	Tool1	Tool1	Tool2	Tool2	Tool2	Tool2	Tool2	Tool3	Tool3	Tool3	Tool3	Tool3	Tool3	Tool4	Tool4	Tool4	Tool4	Tool4	Tool4	Tool4	Tool5	Tool5
3	Tool2	5	9		Row2	Tool5	Tool6	Tool6	Tool6	Tool6	Tool7	Tool7	Tool7	Tool7	Tool7	Tool8	Tool8	Tool8	Tool8	Tool8	Tool8	Tool9	Tool9	Tool10	Tool10	Tool10	Tool11	Tool11	Tool11
4	Tool3	6	15		Row3	Tool11	Tool12	Tool12	Tool12	Tool12	Tool12	Tool13	Tool13	Tool13	Tool13	Tool13	Tool13	Tool14	Tool14	Tool15	Tool15	Tool15	Tool16	Tool16	Tool16	Tool16	Tool17	Tool17	Tool17
5	Tool4	7	22		Row4	Tool17	Tool17	Tool18	Tool18	Tool18	Tool18	Tool18	Tool18	Tool19	Tool19	Tool19	Tool19	Tool19	Tool19	Tool19	Tool20	Tool20	Tool20	Tool20	Tool20	Tool20	Tool20
6	Tool5	3	25
7	Tool6	4	29
8	Tool7	5	34
9	Tool8	6	40
10	Tool9	2	42
11	Tool10	3	45
12	Tool11	4	49
13	Tool12	5	54
14	Tool13	6	60
15	Tool14	2	62
16	Tool15	3	65
17	Tool16	4	69
18	Tool17	5	74
19	Tool18	6	80
20	Tool19	7	87
21	Tool20	7	94
22	Tool21	9	103
23	Tool22	10	113
24	Tool23	11	124
25
Sheet3

Cell Formulas
Range	Formula
F2:AC5	F2	=LET(m,XLOOKUP(24*(ROW(F2:AC5)-2)+COLUMN(F2:AC5)-5,C2:C24,A2:A24,,1),IF(m=XLOOKUP(97,C2:C24,A2:A24,,1), "",m))
C2	C2	=B2
C3:C24	C3	=B3+C2
Dynamic array formulas.

Hi thank you for your reply! Yes that's more or less it!

No screenshot was attached so not entirely sure what you're going for. Maybe:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Tool	Qty	Running Total			Slot1	Slot2	Slot3	Slot4	Slot5	Slot6	Slot7	Slot8	Slot9	Slot10	Slot11	Slot12	Slot13	Slot14	Slot15	Slot16	Slot17	Slot18	Slot19	Slot20	Slot21	Slot22	Slot23	Slot24
2	Tool1	4	4		Row1	Tool1	Tool1	Tool1	Tool1	Tool2	Tool2	Tool2	Tool2	Tool2	Tool3	Tool3	Tool3	Tool3	Tool3	Tool3	Tool4	Tool4	Tool4	Tool4	Tool4	Tool4	Tool4	Tool5	Tool5
3	Tool2	5	9		Row2	Tool5	Tool6	Tool6	Tool6	Tool6	Tool7	Tool7	Tool7	Tool7	Tool7	Tool8	Tool8	Tool8	Tool8	Tool8	Tool8	Tool9	Tool9	Tool10	Tool10	Tool10	Tool11	Tool11	Tool11
4	Tool3	6	15		Row3	Tool11	Tool12	Tool12	Tool12	Tool12	Tool12	Tool13	Tool13	Tool13	Tool13	Tool13	Tool13	Tool14	Tool14	Tool15	Tool15	Tool15	Tool16	Tool16	Tool16	Tool16	Tool17	Tool17	Tool17
5	Tool4	7	22		Row4	Tool17	Tool17	Tool18	Tool18	Tool18	Tool18	Tool18	Tool18	Tool19	Tool19	Tool19	Tool19	Tool19	Tool19	Tool19	Tool20	Tool20	Tool20	Tool20	Tool20	Tool20	Tool20
6	Tool5	3	25
7	Tool6	4	29
8	Tool7	5	34
9	Tool8	6	40
10	Tool9	2	42
11	Tool10	3	45
12	Tool11	4	49
13	Tool12	5	54
14	Tool13	6	60
15	Tool14	2	62
16	Tool15	3	65
17	Tool16	4	69
18	Tool17	5	74
19	Tool18	6	80
20	Tool19	7	87
21	Tool20	7	94
22	Tool21	9	103
23	Tool22	10	113
24	Tool23	11	124
25
Sheet3

Cell Formulas
Range	Formula
F2:AC5	F2	=LET(m,XLOOKUP(24*(ROW(F2:AC5)-2)+COLUMN(F2:AC5)-5,C2:C24,A2:A24,,1),IF(m=XLOOKUP(97,C2:C24,A2:A24,,1), "",m))
C2	C2	=B2
C3:C24	C3	=B3+C2
Dynamic array formulas.

I think screenshot file was too large to upload which is why there was no attachment.
Except the rows are along the top and the slots going down. How

No screenshot was attached so not entirely sure what you're going for. Maybe:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Tool	Qty	Running Total			Slot1	Slot2	Slot3	Slot4	Slot5	Slot6	Slot7	Slot8	Slot9	Slot10	Slot11	Slot12	Slot13	Slot14	Slot15	Slot16	Slot17	Slot18	Slot19	Slot20	Slot21	Slot22	Slot23	Slot24
2	Tool1	4	4		Row1	Tool1	Tool1	Tool1	Tool1	Tool2	Tool2	Tool2	Tool2	Tool2	Tool3	Tool3	Tool3	Tool3	Tool3	Tool3	Tool4	Tool4	Tool4	Tool4	Tool4	Tool4	Tool4	Tool5	Tool5
3	Tool2	5	9		Row2	Tool5	Tool6	Tool6	Tool6	Tool6	Tool7	Tool7	Tool7	Tool7	Tool7	Tool8	Tool8	Tool8	Tool8	Tool8	Tool8	Tool9	Tool9	Tool10	Tool10	Tool10	Tool11	Tool11	Tool11
4	Tool3	6	15		Row3	Tool11	Tool12	Tool12	Tool12	Tool12	Tool12	Tool13	Tool13	Tool13	Tool13	Tool13	Tool13	Tool14	Tool14	Tool15	Tool15	Tool15	Tool16	Tool16	Tool16	Tool16	Tool17	Tool17	Tool17
5	Tool4	7	22		Row4	Tool17	Tool17	Tool18	Tool18	Tool18	Tool18	Tool18	Tool18	Tool19	Tool19	Tool19	Tool19	Tool19	Tool19	Tool19	Tool20	Tool20	Tool20	Tool20	Tool20	Tool20	Tool20
6	Tool5	3	25
7	Tool6	4	29
8	Tool7	5	34
9	Tool8	6	40
10	Tool9	2	42
11	Tool10	3	45
12	Tool11	4	49
13	Tool12	5	54
14	Tool13	6	60
15	Tool14	2	62
16	Tool15	3	65
17	Tool16	4	69
18	Tool17	5	74
19	Tool18	6	80
20	Tool19	7	87
21	Tool20	7	94
22	Tool21	9	103
23	Tool22	10	113
24	Tool23	11	124
25
Sheet3

Cell Formulas
Range	Formula
F2:AC5	F2	=LET(m,XLOOKUP(24*(ROW(F2:AC5)-2)+COLUMN(F2:AC5)-5,C2:C24,A2:A24,,1),IF(m=XLOOKUP(97,C2:C24,A2:A24,,1), "",m))
C2	C2	=B2
C3:C24	C3	=B3+C2
Dynamic array formulas.

Hi thank you for your reply! I think screenshot file was too large to upload which is why there was no attachment. That is more or less it! Except the slots go down and the rows across the top. How would you go about changing the formulas for this? And would you also mind briefly explaining them? I've never seen a LET formula before!

 

[JGordon11]

LET is a function that assigns formulas or values to variable names to make formula writing more concise (among other things). So the variable m is getting assigned that long ugly XLOOKUP,COLUMN,ROW formula. In the final argument of LET the resulting calculation is performed typically using the variables defined in the previous parameters. m is used twice in the final calc so I would have had to repeat the long ugly formula if I didn't use LET.

Note in the example the final two slots are empty since Tool21 has qty of 9 but only two slots remain. If you want two Tool21s in the final two slots the formula would need a minor adjustment (also wouldn't have a repeated formula and so no need for LET function).

Also note that the formula is sensitive to where it is placed and the -6 and -1 would need to be changed if you put anywhere but cell F2. F is the 6th column, driving the -6. If you put it in column H you would use -8. It is in the second row, if you had it in row 5 you would use -4 instead of -1. If you think its position may change because of adding or deleting columns later, I would suggest naming the range (cell) that the formula goes in (say call it pallet) and change formula to:

=LET(m,XLOOKUP(24*(COLUMN(F2:I255)-COLUMN(pallet)+ROW(F2:I25)-ROW(pallet) +1,C2:C24,A2:A24,,1),IF(m=XLOOKUP(97,C2:C24,A2:A24,,1), "",m))

Book2
	A	B	C	D	E	F	G	H	I	J
1	Tool	Qty	Running Total			Row1	Row2	Row3	Row4
2	Tool1	4	4		Slot1	Tool1	Tool5	Tool11	Tool17
3	Tool2	5	9		Slot2	Tool1	Tool6	Tool12	Tool17
4	Tool3	6	15		Slot3	Tool1	Tool6	Tool12	Tool18
5	Tool4	7	22		Slot4	Tool1	Tool6	Tool12	Tool18
6	Tool5	3	25		Slot5	Tool2	Tool6	Tool12	Tool18
7	Tool6	4	29		Slot6	Tool2	Tool7	Tool12	Tool18
8	Tool7	5	34		Slot7	Tool2	Tool7	Tool13	Tool18
9	Tool8	6	40		Slot8	Tool2	Tool7	Tool13	Tool18
10	Tool9	2	42		Slot9	Tool2	Tool7	Tool13	Tool19
11	Tool10	3	45		Slot10	Tool3	Tool7	Tool13	Tool19
12	Tool11	4	49		Slot11	Tool3	Tool8	Tool13	Tool19
13	Tool12	5	54		Slot12	Tool3	Tool8	Tool13	Tool19
14	Tool13	6	60		Slot13	Tool3	Tool8	Tool14	Tool19
15	Tool14	2	62		Slot14	Tool3	Tool8	Tool14	Tool19
16	Tool15	3	65		Slot15	Tool3	Tool8	Tool15	Tool19
17	Tool16	4	69		Slot16	Tool4	Tool8	Tool15	Tool20
18	Tool17	5	74		Slot17	Tool4	Tool9	Tool15	Tool20
19	Tool18	6	80		Slot18	Tool4	Tool9	Tool16	Tool20
20	Tool19	7	87		Slot19	Tool4	Tool10	Tool16	Tool20
21	Tool20	7	94		Slot20	Tool4	Tool10	Tool16	Tool20
22	Tool21	9	103		Slot21	Tool4	Tool10	Tool16	Tool20
23	Tool22	10	113		Slot22	Tool4	Tool11	Tool17	Tool20
24	Tool23	11	124		Slot23	Tool5	Tool11	Tool17
25					Slot24	Tool5	Tool11	Tool17
26
Sheet3

Cell Formulas
Range	Formula
F2:I25	F2	=LET(m,XLOOKUP(24*(COLUMN(F2:I255)-6)+ROW(F2:I25)-1,C2:C24,A2:A24,,1),IF(m=XLOOKUP(97,C2:C24,A2:A24,,1), "",m))
C2	C2	=B2
C3:C24	C3	=B3+C2
Dynamic array formulas.

 

[Mars reject]

Hi thank you for replying. I'll try uploading the screenshot again, I am uploading from mobile so that could be why. I had a look into the XLOOKUP and YLOOKUP formulas but wasn't entirely sure how they worked. Would you be able to explain them? Never used them before.

Hi thank you for your reply! Yes that's more or less it!

I think screenshot file was too large to upload which is why there was no attachment.
Except the rows are along the top and the slots going down. How

Hi thank you for your reply! I think screenshot file was too large to upload which is why there was no attachment. That is more or less it! Except the slots go down and the rows across the top. How would you go about changing the formulas for this? And would you also mind briefly explaining them? I've never seen a LET formula before!

LET is a function that assigns formulas or values to variable names to make formula writing more concise (among other things). So the variable m is getting assigned that long ugly XLOOKUP,COLUMN,ROW formula. In the final argument of LET the resulting calculation is performed typically using the variables defined in the previous parameters. m is used twice in the final calc so I would have had to repeat the long ugly formula if I didn't use LET.

Note in the example the final two slots are empty since Tool21 has qty of 9 but only two slots remain. If you want two Tool21s in the final two slots the formula would need a minor adjustment (also wouldn't have a repeated formula and so no need for LET function).

Also note that the formula is sensitive to where it is placed and the -6 and -1 would need to be changed if you put anywhere but cell F2. F is the 6th column, driving the -6. If you put it in column H you would use -8. It is in the second row, if you had it in row 5 you would use -4 instead of -1. If you think its position may change because of adding or deleting columns later, I would suggest naming the range (cell) that the formula goes in (say call it pallet) and change formula to:

=LET(m,XLOOKUP(24*(COLUMN(F2:I255)-COLUMN(pallet)+ROW(F2:I25)-ROW(pallet) +1,C2:C24,A2:A24,,1),IF(m=XLOOKUP(97,C2:C24,A2:A24,,1), "",m))

Book2
	A	B	C	D	E	F	G	H	I	J
1	Tool	Qty	Running Total			Row1	Row2	Row3	Row4
2	Tool1	4	4		Slot1	Tool1	Tool5	Tool11	Tool17
3	Tool2	5	9		Slot2	Tool1	Tool6	Tool12	Tool17
4	Tool3	6	15		Slot3	Tool1	Tool6	Tool12	Tool18
5	Tool4	7	22		Slot4	Tool1	Tool6	Tool12	Tool18
6	Tool5	3	25		Slot5	Tool2	Tool6	Tool12	Tool18
7	Tool6	4	29		Slot6	Tool2	Tool7	Tool12	Tool18
8	Tool7	5	34		Slot7	Tool2	Tool7	Tool13	Tool18
9	Tool8	6	40		Slot8	Tool2	Tool7	Tool13	Tool18
10	Tool9	2	42		Slot9	Tool2	Tool7	Tool13	Tool19
11	Tool10	3	45		Slot10	Tool3	Tool7	Tool13	Tool19
12	Tool11	4	49		Slot11	Tool3	Tool8	Tool13	Tool19
13	Tool12	5	54		Slot12	Tool3	Tool8	Tool13	Tool19
14	Tool13	6	60		Slot13	Tool3	Tool8	Tool14	Tool19
15	Tool14	2	62		Slot14	Tool3	Tool8	Tool14	Tool19
16	Tool15	3	65		Slot15	Tool3	Tool8	Tool15	Tool19
17	Tool16	4	69		Slot16	Tool4	Tool8	Tool15	Tool20
18	Tool17	5	74		Slot17	Tool4	Tool9	Tool15	Tool20
19	Tool18	6	80		Slot18	Tool4	Tool9	Tool16	Tool20
20	Tool19	7	87		Slot19	Tool4	Tool10	Tool16	Tool20
21	Tool20	7	94		Slot20	Tool4	Tool10	Tool16	Tool20
22	Tool21	9	103		Slot21	Tool4	Tool10	Tool16	Tool20
23	Tool22	10	113		Slot22	Tool4	Tool11	Tool17	Tool20
24	Tool23	11	124		Slot23	Tool5	Tool11	Tool17
25					Slot24	Tool5	Tool11	Tool17
26
Sheet3

Cell Formulas
Range	Formula
F2:I25	F2	=LET(m,XLOOKUP(24*(COLUMN(F2:I255)-6)+ROW(F2:I25)-1,C2:C24,A2:A24,,1),IF(m=XLOOKUP(97,C2:C24,A2:A24,,1), "",m))
C2	C2	=B2
C3:C24	C3	=B3+C2
Dynamic array formulas.

Thank you for explaining this! I was unsure on how to do any of this but was convinced it could be done! I'm still a bit of a rookie with Excel but it never ceases to amaze me just how powerful it is! So essentially you can use LET as something you can use to save a lot of time with long formula writing and with changing parameters? Will have to note this down and save this thread for future reference!

Thanks again for the help with solving my problem as well as bettering my understanding with the powers of excel!