Pick random city names from each risk category in excel

lelis

New Member
Joined
May 16, 2017
Messages
4
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.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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...


A
B
C
D
E
F
G
H
I
J
1
City name​
Risk Level​
Risk​
Desired​
City1​
City2​
City3​
City4​
City5​
2
Abobo​
Extremely Low Risk​
Extremely Low Risk​
3​
Mek'ele​
Enderta​
Raya Azebo​
3
Godere​
Very High Risk​
Very Low Risk​
4​
Dimma​
Abergele​
Jikawo​
Hintalo Wajirat​
4
Dimma​
Very Low Risk​
Low Risk​
4​
Mereb Lehe​
Gambela​
Adwa​
Lare​
5
Gambela​
Low Risk​
Moderate Risk​
5​
Gambela Zuria​
Degua Tembien​
Wanthou​
Alaje​
Naeder Adet​
6
Gambela Zuria​
Moderate Risk​
Highly Moderate Risk​
2​
Alamata​
Enticho​
7
Gog​
Highly Moderate Risk​
High Risk​
2​
OflaVery​
Jor​
8
Jor​
High Risk​
Very High Risk​
1​
Godere​
9
Akobo​
Extremely Low Risk​
10
Jikawo​
Very Low Risk​
11
Lare​
Low Risk​
12
Wanthou​
Moderate Risk​
13
Enderta​
Extremely Low Risk​
14
OflaVery​
High Risk​
15
Hintalo Wajirat​
Very Low Risk​
16
Samre​
Low Risk​
17
Alaje​
Moderate Risk​
18
Alamata​
Highly Moderate Risk​
19
Endamehoni​
High Risk​
20
Raya Azebo​
Extremely Low Risk​
21
Abergele​
Very Low Risk​
22
Adwa​
Low Risk​
23
Degua Tembien​
Moderate Risk​
24
Enticho​
Highly Moderate Risk​
25
Kola Tembien​
High Risk​
26
La'ilay Maychew​
High Risk​
27
Mek'ele​
Extremely Low Risk​
28
Mekele Special Zone​
Very Low Risk​
29
Mereb Lehe​
Low Risk​
30
Naeder Adet​
Moderate Risk​

<tbody>
</tbody>

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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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

New Member
Joined
May 16, 2017
Messages
4
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

New Member
Joined
May 16, 2017
Messages
4
Marcelo,
Nevermind, I just forgot to click CTRL+SHIFT+ENTER. It's working.
Thank you so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,632
Messages
5,597,287
Members
414,134
Latest member
Tiyas44

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top