+ Reply to Thread
Results 1 to 7 of 7

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

  1. 10-20-2011, 03:21 AM #1
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,522

    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 
    Please Login or Register  to view this content.
    ..

    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
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-20-2011, 09:42 AM #2
    Marcol
    Marcol is offline
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

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

    I would go for the Sorted Data option i.e. if you add more cities and towns sort them by City then Town.

    I can only see one case of duplicate towns in your data table (Highlighted with C/F = Orange) is this correct data?

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

    You could avoid duplicates by using suffixes like Hadapsar(1), Hadapsar (east), Hadapsar (Pune), etc....

    Select from the drop-downs in Sheet "Search" Columns B, C $ D
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
    Reply With QuoteRegister To Reply
  3. 10-21-2011, 01:19 AM #3
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,522

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

    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
    Reply With QuoteRegister To Reply
  4. 10-21-2011, 03:27 AM #4
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,522

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

    Dear marcol,

    Defined name = ResultTable
    Please Login or Register  to view this content.
    = TownByLetter
    Please Login or Register  to view this content.
    Please explain these two as other Defined Names are comparatively easier to understand..

    Warm Regards
    e4excel
    Reply With QuoteRegister To Reply
  5. 10-21-2011, 10:41 AM #5
    Marcol
    Marcol is offline
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

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

    =$G$2:INDEX($1:$65535,COUNTA($A:$A),COUNTA($1:$1))

    Read the formula either side of the Range Operator ":" Colon
    Right hand side
    $1:$65535 is a reference to the whole sheet ($1:$1048576 in 2007+)
    COUNTA($A:$A) is the count of all the used cells in Column A
    COUNTA($1:$1) is the count of all the used cells in Row 1
    Note:=
    COUNTA will include any cells with a formula that returns ""

    Index(ref range,row number, col number)
    So Index("The whole Sheet","The last used Row in Column A, ","The last used Column in Row 1")
    In this workbook this returns Range $P$210 (Rather more than we need, but keeps things simple)

    So as $G$2 is the left hand side we get the range $G$2:$P$210

    Read "TownByLetter" the same way this time break down both sides of the operator.
    Reply With QuoteRegister To Reply
  6. 10-23-2011, 04:57 AM #6
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,522

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

    Dear Marcol,

    I tried using the code in your Index formula:

    Please Login or Register  to view this content.
    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
    Reply With QuoteRegister To Reply
  7. 02-09-2012, 01:03 PM #7
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,522

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

    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
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1