Basically, I need to create a drop down list dependent on another drop down selection, but both drop down sources are in the same table that is NOT sorted and is dynamic (users continue to enter new stuff). ie "D4: Dynamic Dependent Drop Downs"

I can't post images yet, so picture a 2 column table of states and cities. Users enter new rows as needed, no sorting. Elsewhere, a drop down allows the user to select a state. The next drop down (the problem) should populate with cities matching that state selection.

I've included a link to an xls with that starting point:
https//drive.google.com/file/d/11RDfeNaQgruEbeIKuhtdEcSabzR7lqUq/view?usp=sharing

The approaches at contextures won't work on unsorted lists, and transposing all data from columns to rows like at Xelplus.com seems way too hacky and will confuse future workbook users/owners and/or won't be maintainable.

I've scratched and googled my way through every approach I can find/contrive and am on the cusp of heading to VBA... before I do though I wanted to see if anyone out there had done this and could share?