Searching a PostCode List for similar names

[Bruce54]

Hi All,

OPSYS:
Mine: Win 7 Pro
Target machines: Win 7 Pro and Win 10

SOFTWARE:
Mine: Excel 2016 (Office 365)
Target machines: Microsoft Office 2010 Professional Plus

I Have a fairly simple spreadsheet for Visitor Recording (I work at a Museum)
The Kiosk Attendant just asks the visitor/customer these questions:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]DATE[/TD]
[TD="align: center"]DAY[/TD]
[TD="align: center"]SUBURB[/TD]
[TD="align: center"]ADULTS[/TD]
[TD="align: center"]CHILDREN[/TD]
[TD="align: center"]SOURCE[/TD]
[TD="align: center"]NOTES[/TD]
[TD="align: center"]POSTCODE[/TD]
[TD="align: center"]STATE[/TD]
[/TR]
[TR]
[TD]01/01/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]VALIDATION[/TD]
[TD][/TD]
[TD]INDEX/MATCH[/TD]
[TD]VLOOKUP[/TD]
[/TR]
[TR]
[TD]02/01/2010[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


1. Where are you from? (Suburb/Town, State and PostCode)
2. How many Adults and Kids in your party?
3. How did you hear about the Museum? (Dropdown with 12 Choices) EASY stuff in data validation!

I have arranged the Date to mirror the one above unless it detects a "1" in the left-adjacent column and increments +1
So far not much complexity.

The PostCode is an Index/Match from the named range (Dependant, blank if SUBURB is not present)
The State is a vlookup of the State (Dependant, blank if PostCode is not present)

The problem arises in the lookup for the Location (Suburb/Town etc)

The list of postcodes for Australia that I have is 8585 rows long.

I worked through the process at https://www.youtube.com/watch?v=srTteYoqcJs
It works, but is soooo slow (1-2 mins to sort itself out) and reacts to each character input instead of after typing.

I tried a "Containing ..." filter of a linked list of the codes on the page, which was lightning fast in comparison
but would require the list to be present on each and every page ( I was unable to remote it to a single page and ...
there's a need, to do such a search EVERY time a new visitor is recorded (basically, each row entered and some months are Huge!)

Straight Data Validation doesn't offer auto-completion, and scrolling thru 8585 lines in a Validation drop-down is Scary, believe me!
and this "Visitors Book" will be handled by various un-skilled persons. Have to apply the KISS principle I'm afraid.

I've been told an Access database would be more suitable, but lack the knowledge in that field, sadly.
I am Limited to the Office Suite, by the Museum itself, please don't suggest other DB products.

I can provide a copy of the spreadsheet (WARNING! File is MACRO-ENABLED Worksheet *.xlsm) if necessary (Haven't found how to yet, but I'm trying )

I am Not tied to the spreadsheet, if an Access solution is better suited, I'm all ears. That said, the auto-complete that occurs after some data has been entered does speed up the Entry Process due to similar reasons in the SOURCE column) if this can be duplicated in ACCESS, I'd be impressed.

Any help or suggestions would be greatly appreciated

 

[BrianJN1]

Obviously a working example would assist someone.
Give some consideration to some of the recommendations offered on site, or some other "cloud" storage like "Dropbox" where you can upload and share.

 

[Bruce54]

Thanks for that

Here is the File

https://www.dropbox.com/s/24y6k4lf7iji8gn/Admission Book (2010).xlsm?dl=0
WARNING! File is MACRO-ENABLED Worksheet *.xlsm - contains macros

Hope it helps

 

[BrianJN1]

Thanks for that Workbook.

I began to understand more about your issue when I attempted to enter a certain location in my area. but discovered that there were 4 instances of that place in the P/Code listings! Thus I never got a P/Code or a State.

If I understand you correctly, you would like that when a cell is selected the pivot table drops down. Then as each character is entered into a cell the list diminishes to the point where a click on the listed entry completes related entries leaving your operator only to enter adult and children numbers as well as Knowledge (Source) of your museum.

For those others who may begin to follow this, "Yarrawonga" was the place I inserted into Bruce's Col C.

I regret Bruce that this is beyond me but that these notes and your Spreadsheet link, directly above, should get what you need.

 

[Bruce54]

Yes, that's correct. The initial system had that bottleneck (Thank You, AustraliaPost - NOT) There are multiple instances of the same town in different states, even towns with multiple Post Codes.
The secondary Search system I implemented shows up the choices using a WildCard search ( "?*&[LOCATION&"?*") ... I was sorta hoping that could be used as the Cell/Column Validation - and then I ran outta Steam (knowledge)
Using a combination of both, it's possible to get the post code, (A) type in Name (B) Hit Search, choose one from the list and finish the Name with " (NSW)" or what ever ... Too complex for a simple user ...
The idea was that it be a dynamic validation drop-down of only the choices presented by the secondary search system

 

[Bruce54]

Sorry, I neglected to mention that in the NOTES column, I placed a temporary formula, one that indicates number found and related states,
thus it's possible to then re-type your town name and add the additional text to it ... e.g YARRAWONGA ... then re-type YARRAWONGA (NT) ... which gives the correct PostCode

 

[Bruce54]

Another addition, my apologies:

The PCODES tab (sheet) has a working search engine, giving a good example of what I'm trying to achieve, Enter the town in the BLUE cell and hit ENTER
It indicates any found and also surrounding towns with the same post code.
Hitting the Blue M+ button shows up variations of the Name entered in the Right-hand Column
I tried running the Variations subroutine as part of the initial search, but it screws up every 2nd attempt. More accurate as 2 separate actions
Warning, the formulae entered in the grey columns are ARRAY formulas

The other functions on the sheet are related to finding different Fire Stations Numbersand their Codes
as well as the ability to enter FROM and TO destinations and Shell out to Google Maps with the provided information for a Driving Route.

 

[BerndKuerzinger]

Hi Bruce54,

Quick question to clarify your objective again: if you were able to get the ComboBox working fast enough with autocomplete as you originally intended - would you want to have 500 of these ComboBoxes on each of the month-tabs in your file?

 

[Bruce54]

Hi Bruce54,

Quick question to clarify your objective again: if you were able to get the ComboBox working fast enough with autocomplete as you originally intended - would you want to have 500 of these ComboBoxes on each of the month-tabs in your file?

I was hoping to avoid that situation. It would seem to be an unnecessary cluttering-up of each page, let alone the entire year for each book.

The working example on the PCODES page is unique, in that it only relies on one input cell. The exisiting State and Postcode lookup is inaccurate, as it ONLY finds the first example of the name, and only if spelt correctly

I updated the PostCode Source List to one that added the State (i.e. "(NSW)" as part of its' name, thus semi-ensuring its unique status. This was only done to the conflicting (duplicate) entries, rather than the whole 8685 records.

I was hoping to be able, somehow, to apply the "possibilites" found from what the User types in (See the working example on the PCODES Page, using the M+ button), as a Column Validation tied to a "Floating" ComboBox. If that makes any sense?

I've used the floating idea before and it works. Using that idea, like a ListFillRange but as the dynamic Source of a Data Validation Rule for the Suburb-Column would be ideal. I just don't know how to do it

I seem to be going around in circles, what I want is dependant upon what I'm trying to do, circular reference --- never a good thing.

 

[Bruce54]

I have found a very usable solution and learned a great deal in the process.

Many thanks to all that showed interest

The Solution revolves around these two videos:
https://www.youtube.com/watch?v=vkPoViUhkxU
https://www.youtube.com/watch?v=0QrQT9D25Xk

particularly the little-documented feature of the CELL("contents") function, copying, as is does, the contents of the last cell editted Even on another Sheet. Brilliant!

Thank You, one and all for your support

I have been unable to find how to close this thread, if that is necessary. Hopefully the moderators will know.