MultiLookup + Unique + Sorted Lists in a Drop-Down in the most faster formula

MultiLookup + Unique + Sorted Lists in a Drop-Down in the most faster formula

Dear Forum,

This query is in connection with my earlier query on the same context..
http://www.excelforum.com/excel-work...ue-values.html

I have 3 Columns:

City, Alphabets, Town..

There are 2 Approaches of Saving the Data one is always Sorting and Maintaining the Chronological Alphabetical Order of the Data and the other is the most natural approach of saving the Data.as and when a New Town is added which is not there in the list it can be simply added in the end with the Name of the city in the Column A and the Alphabet is with the Formula

 = RIGHT(Town,1)

..

Need 2 things one is to get the Functionality of getting the Unique Alphabets in a Sorted Manner just like the List as shown in the Sorted Cities..

The other thing is to get the Selected City and the Alphabet corresponding Town List in the Drop-Down in the Search Sheet..

Warm Regards
e4excel

Thats really good work Marcol,

I thought getting the TownList without using the Indirect Function was impossible but good to be proved wrong as my file is already very slow courtesy INDIRECT and SUMPRODUCT formulas..

If you can aviod duplicate towns then the attached will work, if not we will need array formulae in Sheet "Search" Columns A $ E

I think that record was half-information , but there would be no duplicates as those names can always be changed by Suffixing them with the ( West ) or ( East ) Directions...

I really liked the approach as its is giving the results as conceived but there are a lot of Defined Names so will take time to understand as really pressed for time..wud appreciate if you could explain some of them briefly..please.

but without setting the filters getting the entire thing is possible, then can you please can you help me on the same..

But really very impressive result..

Warm Regards
e4excel

Dear marcol,

Defined name = ResultTable

='Sorted Cities'!$G$2:INDEX('Sorted Cities'!$1:$65535,COUNTA('Sorted Cities'!$A:$A),COUNTA('Sorted Cities'!$1:$1))

= TownByLetter

=INDEX(DataTown,MATCH(Search!$B2&Search!$C2, DataCities&DataAlpha,0),1):INDEX(DataTown,MATCH(Search!$B2&Search!$C2, DataCities&DataAlpha,0)+COUNTIFS(DataCities,Search!$B2,DataAlpha,Search!$C2)-1,1)

Please explain these two as other Defined Names are comparatively easier to understand..

Warm Regards
e4excel

Dear Marcol,

I tried using the code in your Index formula:

COUNTIF(Distribution!$T:$T,">""")

COUNTIF(Distribution!$T:$T,"<""")

But it did not work correctly so I had to try the other way of less than..

I was trying to get the INDEX FUNCTION for the Range:

T3:T9 where the Start Range is T3 and the End Range is Expanding..

Now the Columns HEadings are List in T1 and the Agents No in T2 adn the the Cell T3 contains No 1,2,3,4, 5, 6 etc so on so forth in the cells T4,...T9..

Now I want to have the lsit from T3 till T9 so how do I do it?

and whats the difference between the above 2 COUNTIFS ">" and "<"..

I tried to copy from your logic without understand ing the implication and therefore thought of asking for the explantion..
I think for text it is ">" and for numbers "<"..

Please explain..
Thanks in advance.

Warm regards
e4excel

Just felt like adding something very valuable to this Query..!

https://sites.google.com/a/madrocket...te-single-list

This is by the Forum Guru Jerry Beaucaire's - Excel Assistant