Have an excel spreadsheet with over 45,000 rows, two worksheets, first one is called Users, second is called Cities. Generating fake data for our software, the addresses are fake, however, the cities for each province need to be real and have been generated from real data
Simplified Example: column City is blank in the Users worksheet, a random city would come from the Cities worksheet
Users worksheet:
Name Address City Province
Anne 123 Buckey Street British Columbia
Joe 346 Argyle Road British Columbia
Bill 675 Rollie Avenue Alberta
Tom 348 Gasper Road Saskatchewan
Cities worksheet:
Each Province has a Named Range for all values as the # of rows is different: BC, AB, SK
Across the top created a Named Range of all the provinces: PROVINCES from A1:M1
Another Named range for all the values in Cities is called CANADA
British Columbia Alberta Saskatchewan Manitoba
100 Mile House Alpen Abbott Arnes
Kelowna Bearspaw Canora Digges
Surrey Deer Hill Echo Bay Hilton
Tumbler Ridge Fort Kent La Ronge Paulson
Vancouver Muriel Lake Slave Falls The Pas
Victoria Wastina Yonker Wood Bay
I want to match the Province in each row in the Users Worksheet to the top row in the Cities worksheet, once the province is matched up, pull a random value in that matching column
Example: In the row Joe, a random city would be generated for the column City from the BC named range in the Cities Worksheet
I tried Index(CANADA(MATCH(C2,PROVINCES,1),RAND(BC,AB,SK))
