Forum Discussion

ecomkid's avatar
ecomkid
Copper Contributor
Nov 02, 2022

HELP: ATTACHED SAMPLE DATA, RANDOM AT LEAST ONE OF THE TWO COLUMNS MATCH

Have an excel spreadsheet with over 50,000 rows, two worksheets, first one is called Transactions, second is called Branches which has the Transit Number, and Routing Number for all Branches in each of the provinces. Generating fake data for our software, the Users, Addresses, Credit Card numbers, Postal Codes, CVV, Amounts, and Provinces are fake, the Cities were generated from real data pulled from the Government of Canada. 


Simplified Example: column Routing/Transit Number is blank in the Transactions worksheet, a random Branch would come from the Branches worksheet

Transactions worksheet:

Name Address CityProvince Routing-Transit Number
Anne123 Buckey Street VancouverBritish Columbia  
Joe346 Argyle Road Victoria British Columbia 
Bill675 Rollie Avenue CalgaryAlberta 
Tom348 Gasper RoadMoose JawSaskatchewan 
Gill2218 Faber StTumbler RidgeBritish Columbia 

                                                            
Branches worksheet:
The Column Province has Named Ranges for all values as the # of rows is different per province: ALBERTA,BRITISHCOLUMBIA,SASKATCHEWAN,MANITOBA,ONTARIO

The Column City has multiple instances of the same city as some cities have multiple branches in each city, each branch is in its own row

 

Routing NumberTransit NumberBranch CityProvince
40169901699Signal HillCalgaryAlberta
40171901719Mount RoyalCalgaryAlberta
40172901729MemorialCalgaryAlberta
494090 94090PB Great VanVancouverBritish Columbia
49417094170Fraser & 49thVancouverBritish Columbia
49419094190Main & PenderVancouverBritish Columbia
494400 94400Coal HarbourVancouverBritish Columbia
494480 94480Burrard & DavieVancouverBritish Columbia
47434874348MainMoose Jaw Saskatchewan
47435874358Wal-MartMoose JawSaskatchewan

 

 I want to match each Province and then City in the Transactions the Columns City and Province in the Branches worksheet, once the Province and City are matched up, pull a random Branch, bringing the values of the Routing Number and Transit Number together, if no City is matched up, just pull a random city from the province, and then a random branch from that city if more than 1 branch.


Example: in the row Anne, a random Branch would be generated from the Province of British Columbia and the City matching Vancouver, this City has 81 Branches, so let's say randomly it pulls the branch located at Coal Harbour, 494400-94400 would be pulled into the column Routing/Transit Number in the Transactions Worksheet.

 

 F2 would have 494400-94400 as the value

I tried, #REF error

 =INDEX(INDIRECT(VLOOKUP(G2,ALLPROVINCES,'Branches'!$D$2:$F$2526,4,FALSE)),RANDBETWEEN(1,COUNTA(INDIRECT(VLOOKUP(D2,ALLPROVINCES$D$2:$D$2526,2,FALSE)))))

excel
Formulas and Functions
  • ExcelExciting's avatar
    ExcelExciting
    MVP
    Nov 02, 2022

    Hi,
    Can you please clarify this "if no City is matched up, just pull a city and then a Random branch from that City"? do you mean here is a Random City from the list?
    As in your above dataset of Branche's worksheet, there is NO city called Victoria. How would you like the results to appear?

    Regards, Faraz Shaikh,
    Microsoft MVP, Excel Expert, MCT, MIE

    • ecomkid's avatar
      ecomkid
      Copper Contributor
      Nov 02, 2022
      In the case of Victoria, there are 21 branches, but some of the smaller towns that were generated in the transactions do not have a listing in the branches, so once the Province matches, if no City is found, a random city/branch is pulled from the Province.

      So an example is one of the transactions in British Columbia, Tumbler Ridge, does not have a branch in the town/city, so just want a random branch pulled where the Province is equal to British Columbia.
  • patellaptop2008's avatar
    patellaptop2008
    Copper Contributor
    Sep 16, 2024
    No No No No Address City Province
    72 73 74 75 200-2700 Main St South Airdrie Alberta
    73 480309 80309 Airdrie Downtown 505 Main St S Unit 200 Airdrie Alberta
    74 480329 80329 Barrhead 5037-50 ST Barrhead Alberta
    256 488969 88969 BARRHEAD 5037- 50TH Street, 4599 Barrhead Alberta
    147 482089 82089 Beaumont 6000 50th St. Beaumont Alberta
    78 480489 80489 Bonnyville 4919 50th Avenue, PO Box 6308 Bonnyville Alberta
    79 480509 80509 Brooks 1040 2nd Street West Brooks Alberta
    6 400599 599 Calgary 4935 40 Ave NW Mkt Mall 40 Ave NW Unit 132 Calgary Alberta
    7 401049 1049 Calgary Willow Park 10816 Macleod Trail SE Calgary Alberta
    10 401489 1489 Calgary Data Centre Management 1216 10th Ave SW 1st Floor, P O Box 2141 (Calgary Data Ctre.) Calgary Alberta
    12 401699 1699 Signal Hill Centre 5680 Signal Hill Centre SW Calgary Alberta
    13 401719 1719 Calgary Mount Royal 915 17 Ave SW Calgary Alberta
    27 402289 2289 Calgary N Hill SC 1632 14th Ave NW No 1774 Calgary Alberta
    29 402589 2589 Calgary 90th Ave 90th Ave SW Calgary Alberta
    31 402899 2899 Calgary Eaton Centre 317 7th Avenue SW Calgary Alberta
    32 402909 2909 Calgary Beddington 8118 Beddington Blvd NW Calgary Alberta
    33 402919 2919 Calgary Brentwood 3630 Brentwood Rd NW Calgary Alberta
    34 402929 2929 Calgary Marlborough 4415 Memorial Drive SE Calgary Alberta
    181 483329 83329 Northwood 13711-93 St NW Edmonton Alberta
    182 483339 83339 Millwoods 2505 Hewes Way Edmonton Alberta
    183 483369 83369 Edmonton Westmount Centre 11210 Groat Road Edmonton Alberta
    184 483489 83489 Harvest Pointe 5033 Ellerslie RD SW Edmonton Alberta
    185 483519 83519 Agri Indirect Fin 1 500 Edmonton City Centre 5th Flr WCC Edmonton Alberta
    337 403930 3930 Burnaby Hastings 4298 East Hastings Street Vancouver British Columbia
    338 404160 4160 Dunsmuir & Burrard 1055 Dunsmuir St. Vancouver British Columbia
    377 486740 86740 CAD CC FOR 08673 700 WEST GEORGIA ST 3RD FLOOR, TD TOWER Vancouver British Columbia
    379 490030 90030 Vancouver Core 700 W Georgia St, P.O. Box 10001 Vancouver British Columbia
    380 490060 90060 Coquitlam/Burnaby 700 West Georgia St. Vancouver British Columbia
    580 499140 99140 Victoria Centre Branch 1080 Douglas St, P O Box 1420 Victoria British Columbia
    581 499150 99150 Westshore Town Centre 2945 Jacklin Rd Unit 860 Victoria British Columbia
    583 499190 99190 Westside Village 182 Wilson Street Unit 100 Victoria British Columbia
    584 499550 99550 Burnside & Tillicum 309 Burnside Rd W Victoria British Columbia
    585 499560 99560 Saanich Plaza 3530 Blanshard St Victoria British Columbia
    2469 474328 74328 - 145 Main St N Moose Jaw Saskatchewan
    2470 474348 74348 Moose Jaw Branch 145 Main St N Moose Jaw Saskatchewan
    2471 474358 74358 Wal-Mart - Moose Jaw 145 Main Street North Moose Jaw Saskatchewan
    2476 475088 75088 Prince Albert 801 15th St E Unit 601 Prince Albert Saskatchewan
    2477 475098 75098 Prince Albert 801 15th St E Unit 601 Prince Albert Saskatchewan
    2445 400038 38 Hamilton St & 12th 1904 Hamilton St. Regina Saskatchewan
    2448 402088 2088 Lakeview Plaza 4240 Albert Street Regina Saskatchewan
    2449 402548 2548 Sherwood Mall 4011 Rochdale Blvd Unit E Regina Saskatchewan

Resources