Adding city names by rows

[lelis]

[TABLE="width: 1120"]
<colgroup><col width="106" style="width:80pt"> <col width="161" style="width:121pt"> <col width="46" style="width:35pt"> <col width="160" style="width:120pt"> <col width="64" style="width:48pt"> <col width="106" style="width:80pt"> <col width="124" style="width:93pt"> <col width="147" style="width:110pt"> <col width="142" style="width:107pt"> <col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl59, width: 106"] City[/TD]
[TD="class: xl59, width: 161"]Risk Level[/TD]
[TD="class: xl59, width: 46"][/TD]
[TD="class: xl59, width: 160"]Risk Levels[/TD]
[TD="class: xl59, width: 64"]Desired[/TD]
[TD="class: xl59, width: 106"] City 1[/TD]
[TD="class: xl59, width: 124"]City 2[/TD]
[TD="class: xl59, width: 147"]City 3[/TD]
[TD="class: xl59, width: 142"]City 4[/TD]
[TD="class: xl59, width: 64"]City 5[/TD]
[/TR]
[TR]
[TD]Abobo[/TD]
[TD]Extremely Low Risk[/TD]
[TD][/TD]
[TD]Extremely Low Risk[/TD]
[TD="align: right"]3[/TD]
[TD] Abobo[/TD]
[TD]Enderta[/TD]
[TD]Raya Azebo[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Godere[/TD]
[TD]Very High Risk[/TD]
[TD][/TD]
[TD]Very Low Risk[/TD]
[TD="align: right"]4[/TD]
[TD] Dimma[/TD]
[TD]Abergele[/TD]
[TD]Mekele Special Zone[/TD]
[TD]Hintalo Wajirat[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dimma[/TD]
[TD]Very Low Risk[/TD]
[TD][/TD]
[TD]Low Risk[/TD]
[TD="align: right"]4[/TD]
[TD] Adwa[/TD]
[TD]Mereb Lehe[/TD]
[TD]Samre[/TD]
[TD]Lare[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gambela [/TD]
[TD]Low Risk[/TD]
[TD][/TD]
[TD]Moderate Risk[/TD]
[TD="align: right"]5[/TD]
[TD] Gambela Zuria[/TD]
[TD]Degua Tembien[/TD]
[TD]Alaje[/TD]
[TD]Naeder Adet[/TD]
[TD]Wanthou[/TD]
[/TR]
[TR]
[TD]Gambela Zuria[/TD]
[TD]Moderate Risk[/TD]
[TD][/TD]
[TD]Highly Moderate Risk[/TD]
[TD="align: right"]2[/TD]
[TD] Gog[/TD]
[TD]Enticho[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gog[/TD]
[TD]Highly Moderate Risk[/TD]
[TD][/TD]
[TD]High Risk[/TD]
[TD="align: right"]2[/TD]
[TD] Endamehoni[/TD]
[TD]Jor[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jor[/TD]
[TD]High Risk [/TD]
[TD][/TD]
[TD]Very High Risk[/TD]
[TD="align: right"]1[/TD]
[TD] Godere[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Akobo[/TD]
[TD]Extremely Low Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jikawo[/TD]
[TD]Very Low Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lare[/TD]
[TD]Low Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wanthou[/TD]
[TD]Moderate Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Enderta[/TD]
[TD]Extremely Low Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ofla[/TD]
[TD]Very High Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hintalo Wajirat[/TD]
[TD]Very Low Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Samre[/TD]
[TD]Low Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alaje[/TD]
[TD]Moderate Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alamata[/TD]
[TD]Highly Moderate Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Endamehoni[/TD]
[TD]High Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Raya Azebo[/TD]
[TD]Extremely Low Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Abergele[/TD]
[TD]Very Low Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Adwa[/TD]
[TD]Low Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Degua Tembien[/TD]
[TD]Moderate Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Enticho[/TD]
[TD]Highly Moderate Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kola Tembien[/TD]
[TD]High Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]La'ilay Maychew[/TD]
[TD]Very High Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mek'ele[/TD]
[TD]Extremely Low Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mekele Special Zone[/TD]
[TD]Very Low Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mereb Lehe[/TD]
[TD]Low Risk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Naeder Adet[/TD]
[TD]Moderate Risk
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi, I used this formula to get the desired cities randomly from the City column. However the cities desired increment by columns (horizontally) instead of by rows(Vertically). I want the cities to be added vertically. For ex:

[TABLE="width: 1120"]
<tbody>[TR]
[TD="class: xl59, width: 106"]Desired City 1[/TD]
[TD="class: xl59, width: 161"][/TD]
[TD="class: xl59, width: 46"][/TD]
[TD="class: xl59, width: 160"][/TD]
[TD="class: xl59, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
3 Adobo
Enderta
Raya Azebo

This is the formula I used: =IF(COLUMNS($F2:F2)>$E2,"",INDEX($A:$A,SMALL(IF($B$2:$B$30=$D2,IF(ISNA(MATCH($A$2:$A$30,$E2:E2,0)),ROW($B$2:$B$30),0),0),RANDBETWEEN(COUNTIF($B$2:$B$30,"<>"&$D2)+COLUMNS($F2:F2),ROWS($B$2:$B$30)))))

Please let me know if there is a way to do this.

Thank you very much in advance.

 

[chleb]

Hi,

I found some solution, but to understand it You should probably look into my sheet in Excel Online:
https://1drv.ms/x/s!AlDc4uNa3Ao8gypyQf0aeSpV7HwD

Here are the formulas used in the solution:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G	H	I
1	City	Risk Level							Risk Levels
2	Abobo	Extremely Low Risk		0.142876		Risk level	Very High Risk		Extremely Low Risk
3	Godere	Very High Risk		0.406627		Desired	5		Very Low Risk
4	Dimma	Very Low Risk		0.634598		Cities	Ofla		Low Risk
5	Gambela	Low Risk		0.590462			Godere		Moderate Risk
6	Gambela Zuria	Moderate Risk		0.929963			La'ilay Maychew		Highly Moderate Risk
7	Gog	Highly Moderate Risk		0.828701					High Risk
8	Jor	High Risk		0.12128					Very High Risk
9	Akobo	Extremely Low Risk		0.612942
10	Jikawo	Very Low Risk		0.984366
11	Lare	Low Risk		0.632343
12	Wanthou	Moderate Risk		0.662796
13	Enderta	Extremely Low Risk		0.340894
14	Ofla	Very High Risk		0.896094
15	Hintalo Wajirat	Very Low Risk		0.793369
16	Samre	Low Risk		0.4936
17	Alaje	Moderate Risk		0.794289
18	Alamata	Highly Moderate Risk		0.876628
19	Endamehoni	High Risk		0.921099
20	Raya Azebo	Extremely Low Risk		0.996822
21	Abergele	Very Low Risk		0.02916
22	Adwa	Low Risk		0.518003
23	Degua Tembien	Moderate Risk		0.763648
24	Enticho	Highly Moderate Risk		0.55265
25	Kola Tembien	High Risk		0.881788
26	La'ilay Maychew	Very High Risk		0.107096
27	Mek'ele	Extremely Low Risk		0.420331
28	Mekele Special Zone	Very Low Risk		0.372463
29	Mereb Lehe	Low Risk		0.075304
30	Naeder Adet	Moderate Risk		0.394132
Sheet3

Cell Formulas
Range	Formula
D2	=RAND()
G4	{=IFERROR(IF(ROWS($F$4:F4)>$G$3,"",INDEX($A$2:$A$30,MATCH(LARGE(($B$2:$B$30=$G$2)*($D$2:$D$30),ROWS($F$4:F4)),$D$2:$D$30,0))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

I hope this solves the problem.
Have a nice day.