Help with sorting data

[PJSR]

Hi,

Not sure if VB script can help.

Need help from the experts or else it's going to be a very time consuming manual process for me. The reason being that there are 1000?s of customers and more than 2000 suburbs.

That said I will try to explain as best I can. Also attached an example.

I received customer lists reflecting the actual suburb the customer can be found in. These were created manually and is therefore subject to spelling etc. (Example B2-B11)

How can I:

1) Get Excel to validate Items in B1-11 with items A23-33 (A22-33 reflects the correct spelling) and then if there is a 100% match reflect the suburb in C2-11. If there is no 100% match then give me the choice of closest possible matches based on A23-33? Like I said there are more than 2000 suburbs and this could save some time.
2) Once the validation is complete and I selected or entered an option for an item that does not match this with A23-33 and reflect the geographic area as defined by B23-33 in D2-11?

I need to sort this in to predefined geographical areas. A geographical area is made up of several suburbs and can change.

Hope someone understands??


Please help.

Thanks

[PJSR]

A see some views to the thread but should I take it from the no replies that there's no way of doing this?

[rory]

The only tricky bit is finding the closest match where there are spelling mistakes or variations in the suburb name. The rest is simple lookup formulas.
I suspect it would be as easy to create a translation table that you can use in a lookup formula. If you use the Advanced Filter option to get a list of the unique suburb entries received, you can then sort it and enter the actual suburb name in a column alongside. Hopefully most of the variations will appear near each other so you can enter the real name for all of them at once. Once you have created this table, it should be a smaller job going forward to add new variations to the table and use this as a master lookup table.