Sequencing cars depending on numbers entered

[Bentley_Wood]

I am trying to create a spreadsheet which will automatically sequence cars depending on how many there are.

For example I want to enter:

SUV- 4
GT - 7

and the spreadsheet would create a sequence like this:

SUV, GT, GT, SUV, GT, GT, SUV, GT, SUV, GT, GT

Where possible the sequence would be:

SUV, GT, GT, SUV, GT, GT

But it could be this:

SUV, GT, SUV, GT

I hope this makes sense!

 

[mole999]

what would the priority be, its not really evident as to why apart from more GT, the next set might have more SUV but the criteria wouldn't hold, so how do you decide, and what could be translated into machine learning

 

[Bentley_Wood]

what would the priority be, its not really evident as to why apart from more GT, the next set might have more SUV but the criteria wouldn't hold, so how do you decide, and what could be translated into machine learning

Thanks for your reply.

Priority is to mix the cars evenly through the shift so all SUV and all GT are not together.

 

[FormR]

But it could be this:

SUV, GT, SUV, GT

Hi, welcome to the forum - here is one option you can try.

Excel 2013/2016
	A	B	C
1	SUV	4	SUV, GT, SUV, GT, SUV, GT, SUV, GT, GT, GT, GT
2	GT	7
Sheet3

Cell Formulas
Range	Formula
C1	=MID(REPT(", "&A1&", "&A2,MIN(B1:B2))&REPT(", "&IF(B1>B2,A1,A2),ABS(B1-B2)),3,999)

 

[Bentley_Wood]

Hi, welcome to the forum - here is one option you can try.

Excel 2013/2016

	A	B	C
SUV
GT

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]4[/TD]
[TD="bgcolor: #FFFF00"]SUV, GT, SUV, GT, SUV, GT, SUV, GT, GT, GT, GT[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=MID(REPT(", "&A1&", "&A2,MIN(B1:B2))&REPT(", "&IF(B1>B2,A1,A2),ABS(B1-B2)),3,999)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks, is it possible to make this formula put each SUV/GT in a separate cell and to also distribute them evenly so there are not 4x GT at the end?

 

[FormR]

Thanks, is it possible to make this formula put each SUV/GT in a separate cell and to also distribute them evenly so there are not 4x GT at the end?

You could try this - it makes use of a few helper cells to simply things a little, formula in E2 can be copied down as far as may be required.

Excel 2013/2016
	A	B	C	D	E
1	SUV	5			GT
2	GT	7			SUV
3					GT
4	GT	7			SUV
5	SUV	5			GT
6	Total	12			SUV
7	Ratio	1			GT
8					SUV
9					GT
10					SUV
11					GT
12					GT
Sheet3

Cell Formulas
Range	Formula
A4	=IF(B1>B2,A1,A2)
A5	=IF(B1<=B2,A1,A2)
B4	=MAX(B1:B2)
B5	=MIN(B1:B2)
B6	=SUM(B4:B5)
B7	=INT(B4/B5)
E1	=IF(ROWS(E$1:E1)>$B$6,"",IF(MOD(ROWS(E$1:E1),$B$7+1),$A$4,IF((ROWS(E$1:E1)-1)/(1+$B$7)>$B$5,$A$4,$A$5)))

 

[Bentley_Wood]

Thanks, the only issue I have with this is sometimes it will still have a list of consecutive SUV or GT at the end.

For example when entering 7 SUV and 13 GT you get 6 consecutive GTs at the end. Is there a way to distribute these evenly?

 

[FormR]

Is there a way to distribute these evenly?

Hi, this is as good as I can get.

Excel 2013/2016
	A	B	C	D	E
1	SUV	7			GT
2	GT	13			GT
3					SUV
4	GT	13			GT
5	SUV	7			GT
6	Total	20			SUV
7	Ratio	2			GT
8					GT
9					SUV
10					GT
11					GT
12					SUV
13					GT
14					GT
15					SUV
16					GT
17					GT
18					SUV
19					GT
20					SUV
Sheet1

Cell Formulas
Range	Formula
A4	=IF(B1>B2,A1,A2)
A5	=IF(B1<=B2,A1,A2)
B4	=MAX(B1:B2)
B5	=MIN(B1:B2)
B6	=SUM(B4:B5)
B7	=ROUND(B4/B5,0)
E1	=IF(ROWS(E$1:E1)>$B$6,"",IF(MOD(ROWS(E$1:E1),$B$7+1),IF((ROWS(E$1:E1))/($B$7+1)*$B$7>$B$4,$A$5,$A$4),IF((ROWS(E$1:E1)-1)/(1+$B$7)>$B$5,$A$4,$A$5)))

 

[mole999]

I've been watching this and wondering is it extensible without work for four, six, nine etc vehicles

 

[FormR]

wondering is it extensible without work for four, six, nine etc vehicles

Hi, if you mean more that just SUV and GT then no, it is not extensible - it would be starting from scratch.