Pick random city names from each risk category in excel

[lelis]

Hi, I am trying to pick random city names from a list of very large data in excel, each city has different categories. I want to pick 23 city names randomly (3 from extremely low risk, 4 from very low risk, 4 from low risk, 5 from moderate risk, 2 from highly moderate risk, 2 from high risk and 1 from very high risk. Is there a formula in excel that can choose 3 cities that are extremely low risk randomly, 4 cities from low risk, 5 cities from moderate risk...and so on? (the data below is just a part of the data I have in the sheet)
City Name Risk Level
AboboExtremely Low Risk
GodereVery High Risk
DimmaVery Low Risk
Gambela Low Risk
Gambela Zuria Moderate Risk
GogHighly Moderate Risk
Jor High Risk
AkoboExtremely Low Risk
JikawoVery Low Risk
Lare Low Risk
Wanthou Moderate Risk
EndertaExtremely Low Risk
OflaVery High Risk
Hintalo WajiratVery Low Risk
Samre Low Risk
Alaje Moderate Risk
AlamataHighly Moderate Risk
Endamehoni High Risk
Raya AzeboExtremely Low Risk
AbergeleVery Low Risk
Adwa Low Risk
Degua Tembien Moderate Risk
EntichoHighly Moderate Risk
Kola Tembien High Risk
La'ilay MaychewVery High Risk
Mek'eleExtremely Low Risk
Mekele Special Zone Very Low Risk
Mereb Lehe Low Risk
Naeder Adet Moderate Risk

Any help will be very helpful. Thank you so much in advance.

 

[Marcelo Branco]

Hi, I am trying to pick random city names from a list of very large data in excel, each city has different categories. I want to pick 23 city names randomly ( 3 from extremely low risk, 4 from very low risk, 4 from low risk, 5 from moderate risk, 2 from highly moderate risk, 2 from high risk and 1 from very high risk.

I think you meant 21 cities: 3+4+4+5+2+2+1 - but you can easily adapt the example below for more/less cities

Maybe something like this...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

City name​

[/TD]
[TD]

Risk Level​

[/TD]
[TD][/TD]
[TD]

Risk​

[/TD]
[TD]

Desired​

[/TD]
[TD]

City1​

[/TD]
[TD]

City2​

[/TD]
[TD]

City3​

[/TD]
[TD]

City4​

[/TD]
[TD]

City5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Abobo​

[/TD]
[TD]

Extremely Low Risk​

[/TD]
[TD][/TD]
[TD]

Extremely Low Risk​

[/TD]
[TD]

3​

[/TD]
[TD]

Mek'ele​

[/TD]
[TD]

Enderta​

[/TD]
[TD]

Raya Azebo​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Godere​

[/TD]
[TD]

Very High Risk​

[/TD]
[TD][/TD]
[TD]

Very Low Risk​

[/TD]
[TD]

4​

[/TD]
[TD]

Dimma​

[/TD]
[TD]

Abergele​

[/TD]
[TD]

Jikawo​

[/TD]
[TD]

Hintalo Wajirat​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Dimma​

[/TD]
[TD]

Very Low Risk​

[/TD]
[TD][/TD]
[TD]

Low Risk​

[/TD]
[TD]

4​

[/TD]
[TD]

Mereb Lehe​

[/TD]
[TD]

Gambela​

[/TD]
[TD]

Adwa​

[/TD]
[TD]

Lare​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Gambela​

[/TD]
[TD]

Low Risk​

[/TD]
[TD][/TD]
[TD]

Moderate Risk​

[/TD]
[TD]

5​

[/TD]
[TD]

Gambela Zuria​

[/TD]
[TD]

Degua Tembien​

[/TD]
[TD]

Wanthou​

[/TD]
[TD]

Alaje​

[/TD]
[TD]

Naeder Adet​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Gambela Zuria​

[/TD]
[TD]

Moderate Risk​

[/TD]
[TD][/TD]
[TD]

Highly Moderate Risk​

[/TD]
[TD]

2​

[/TD]
[TD]

Alamata​

[/TD]
[TD]

Enticho​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Gog​

[/TD]
[TD]

Highly Moderate Risk​

[/TD]
[TD][/TD]
[TD]

High Risk​

[/TD]
[TD]

2​

[/TD]
[TD]

OflaVery​

[/TD]
[TD]

Jor​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Jor​

[/TD]
[TD]

High Risk​

[/TD]
[TD][/TD]
[TD]

Very High Risk​

[/TD]
[TD]

1​

[/TD]
[TD]

Godere​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[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="bgcolor: #DCE6F1"]

10​

[/TD]
[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="bgcolor: #DCE6F1"]

11​

[/TD]
[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="bgcolor: #DCE6F1"]

12​

[/TD]
[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="bgcolor: #DCE6F1"]

13​

[/TD]
[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="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

OflaVery​

[/TD]
[TD]

High Risk​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[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="bgcolor: #DCE6F1"]

16​

[/TD]
[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="bgcolor: #DCE6F1"]

17​

[/TD]
[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="bgcolor: #DCE6F1"]

18​

[/TD]
[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="bgcolor: #DCE6F1"]

19​

[/TD]
[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="bgcolor: #DCE6F1"]

20​

[/TD]
[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="bgcolor: #DCE6F1"]

21​

[/TD]
[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="bgcolor: #DCE6F1"]

22​

[/TD]
[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="bgcolor: #DCE6F1"]

23​

[/TD]
[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="bgcolor: #DCE6F1"]

24​

[/TD]
[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="bgcolor: #DCE6F1"]

25​

[/TD]
[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="bgcolor: #DCE6F1"]

26​

[/TD]
[TD]

La'ilay Maychew​

[/TD]
[TD]

High Risk​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

27​

[/TD]
[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="bgcolor: #DCE6F1"]

28​

[/TD]
[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="bgcolor: #DCE6F1"]

29​

[/TD]
[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="bgcolor: #DCE6F1"]

30​

[/TD]
[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]

Criteria (risks) in D2:D8
Desired quantities in E2:E8

Array formula in F2 coopied across till J2 and down
=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)))))

confirmed with Ctrl+Shift+Enter, not just Enter

Press F9 to get random cities

Hope this helps

M.

 

[Marcelo Branco]

Or this a little bit shorter array formula

F2 copied across and down
=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))),RANDBETWEEN(1,COUNTIF($B$2:$B$30,$D2)-COLUMNS($F2:F2)+1))))
Ctrl+Shift+Enter

M.

 

[lelis]

Or this a little bit shorter array formula

F2 copied across and down
=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))),RANDBETWEEN(1,COUNTIF($B$2:$B$30,$D2)-COLUMNS($F2:F2)+1))))
Ctrl+Shift+Enter

M.

Hi Marcelo, Thank you so much for your help on this. I used the above formula but for some reason it is showing error when I use it. (#NUM!) Do you know why? Thank you again for your help!

 

[lelis]

Marcelo,
Nevermind, I just forgot to click CTRL+SHIFT+ENTER. It's working.
Thank you so much!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.