Attached a new sample spreadsheet with examples, Stop removing sample fake data spreadsheet. Please read.
ALL DATA IS FAKE from an online data generator. The Cities and Provinces are real, from the Government of Canada, the branches are from TD Bank and are public to add authenticity for testing purposes, all DATA IS NOT REAL, IT IS FAKE.
CREDIT CARD NUMBERS FAKE, CVV FAKE. Hopefully people will read before removing sample fake data worksheet.
NAMES AND ADDRESSES GENERATED FROM A FAKE GENERATOR, REMOVED OUT OF SAMPLE AS NOT NEEDED. PM me as I created a code that combines First and Last Name, has modifiers between including "-", "_" and "." as well as 12 different numbers, and 13 different email service providers. Unable to post here as I am too new and it keeps giving me an error in my submission.
Formula used for those than want to use in the future:
=A2&(CHOOSE(RANDBETWEEN(1,3),".","-","_")&B2&(CHOOSE(RANDBETWEEN(1,10),"123","456","789","223","445","4152","991","862","742","6234")&"@"&(CHOOSE(RANDBETWEEN(1,10),"aol.com","gmail.com","gmx.com","icloud.com","hotmail.com","outlook.com","protonmail.com","yandex.com","zoho.com","zohomail.com")))
It is the Routing Number and Transit number I am looking for in the Transactions Worksheet, see attached with examples.
Excel spreadsheet with over 50,000 rows, two worksheets, first one is called Transactions, second is called Branches
I am trying to find the routing number and transit number where the Province and City match, however:
1. IF the Province matches and the City is not found, a random city chosen, if there are multiple branches in that city, then a random branch is pulled.
2. IF the Province and the City match, a random branch is chosen if there is more than 1 branch in the city.
Named Ranges: each Province has a named Range in Branches: BRITISHCOLUMBIA,ALBERTA,SASKATCHEWAN
Column Routing/Transit Number is blank in the Transactions worksheet, a random Branch would come from the Branches worksheet
I tried, #REF error, so I know I have it wrong. Tried VLOOKUP, but want to be able to choose a RANDOM branch if the City has more than one.
Not sure if this correct or what I am missing or if I have gone about this all wrong as far as formula. Do not want VBA as I will modifying this formula for another instance similar to what is needed now.
*=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)))))
