Adding city names by rows

[lelis]

City	Risk Level		Risk Levels	Desired	City 1	City 2	City 3	City 4	City 5
Abobo	Extremely Low Risk		Extremely Low Risk	3	Abobo	Enderta	Raya Azebo
Godere	Very High Risk		Very Low Risk	4	Dimma	Abergele	Mekele Special Zone	Hintalo Wajirat
Dimma	Very Low Risk		Low Risk	4	Adwa	Mereb Lehe	Samre	Lare
Gambela	Low Risk		Moderate Risk	5	Gambela Zuria	Degua Tembien	Alaje	Naeder Adet	Wanthou
Gambela Zuria	Moderate Risk		Highly Moderate Risk	2	Gog	Enticho
Gog	Highly Moderate Risk		High Risk	2	Endamehoni	Jor
Jor	High Risk		Very High Risk	1	Godere
Akobo	Extremely Low Risk
Jikawo	Very Low Risk
Lare	Low Risk
Wanthou	Moderate Risk
Enderta	Extremely Low Risk
Ofla	Very High Risk
Hintalo Wajirat	Very Low Risk
Samre	Low Risk
Alaje	Moderate Risk
Alamata	Highly Moderate Risk
Endamehoni	High Risk
Raya Azebo	Extremely Low Risk
Abergele	Very Low Risk
Adwa	Low Risk
Degua Tembien	Moderate Risk
Enticho	Highly Moderate Risk
Kola Tembien	High Risk
La'ilay Maychew	Very High Risk
Mek'ele	Extremely Low Risk
Mekele Special Zone	Very Low Risk
Mereb Lehe	Low Risk
Naeder Adet	Moderate Risk

<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>
</tbody>

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:

Desired City 1

<tbody>
</tbody>

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.