# Pick random city names from each risk category in excel

#### lelis

##### New Member
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
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

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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Marcelo Branco

##### MrExcel MVP
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
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
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
Marcelo,
Nevermind, I just forgot to click CTRL+SHIFT+ENTER. It's working.
Thank you so much!

#### Marcelo Branco

##### MrExcel MVP
You are welcome. Glad to help.

M.

Replies
1
Views
263

1,127,204
Messages
5,623,354
Members
415,969
Latest member
Rey99

### 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.

### Which adblocker are you using?

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

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