[GoogleSheets to TW5] Choosing a random suburb and street from a specified random city

[Hegart Dmishiv]

I'm cross-posting this from the Google Sheets API group, where I posted it yesterday. Maybe some of my TiddlyWiki friends here can help me with this, although it isn't strictly TW-related. I'm creating this sample data (example TW here) for our {{DesignWrite}} semester project. Here's what I posted in the other group...


I'm working on a little project which necessitates creating some random sample data for testing purposes. The data is based on New Zealand localised demographic information. I've got the 'Person' sheet working perfectly, using the INDEX() and RANDBETWEEN() functions to select random surnames and first names, even gender-specific first names. Now I'm working on the 'Address' sheet. I want to build random addresses using the same methodology, based on the information in columns E to H of the 'Random Source' sheet.

As you'll see in my sheet, I have successfully selected a random city. That was the easy part. Now I need to select a random suburb within that city, and a random street within that suburb. I presume I'll need to use something like the VLOOKUP(), QUERY(), or GETPIVOTDATA() functions, but I'm not sure which, and I'm not sure how. Any advice would really be appreciated.

I have made a spare copy of my Google Sheet for demonstration purposes and shared it for editing here.

Thanks in advance,

Hegart.

[Hegart Dmishiv]

Hi again,

I've sorted this out for myself now. I created a basic pivot table, and used VLOOKUP() to query against that. Probably not the "proper" way of doing it, but it works for me. So, thanks anyway, I'm disabling that share to my example sheet, as I'll be recycling it.

Hegart.