Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: dropdown list in alphabetical order data validation...

  1. #1
    Board Regular albertc30's Avatar
    Join Date
    May 2012
    Location
    Norfolk
    Posts
    969
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default dropdown list in alphabetical order data validation...

    Hi all, again...

    I have been reading on this and have found a few good youtube videos.

    One in particular mentioned 4 ways this could be achieved, the best way being the new function SORT.

    Sadly, this is not available on my newly bought office 2019 professional. Just another way Microsoft has to push us in the direction they want us to go.

    Another way to do it was by means of a query. To be honest I never have worked with queries and the one thing about it was that it needed refreshing manually?!

    There's also, off course the macro. To use this I'd have to copy the column in question to another sheet as to have it sorted independently and only the data I'm interested in.

    Is there a better simpler way to achieve this?

    Any help is truly appreciated.

    Thank you.
    Cheers
    Many thanks for all the help, much appreciated.

    Regards,
    Albert

    Reminder
    Remember to use code tags people.

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,886
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    What are you actually trying to do ?
    A bit more info about the problem may help, not what you think might be the solution ?
    Last edited by Michael M; Aug 23rd, 2019 at 10:07 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,390
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    There is no innate way that Excel can take an unordered list on a worksheet and cause it to suddenly appear ordered in a data validation drop-down. So whether you were using the (unavailable) SORT function, query, macro or manually copying & sorting or anything else you would need to have the sorted list stored somewhere.

    Is there a better simpler way? Well, assuming no duplicates are to be included in the list you could try this standard formula, copied down. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

    This sorted list could be in a hidden column or on another sheet if you want and used for your DV list. If you were to subsequently change cat for zebra the sorted list would automatically update.

    Alphabetical

    ABC
    1dog cat
    2cat dog
    3mouse horse
    4horse mouse

    Spreadsheet Formulas
    CellFormula
    C1{=INDEX(A$1:A$4,MATCH(ROWS(C$1:C1),COUNTIF(A$1:A$4,"<"&A$1:A$4)+1,0))}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

    If you might be adding to or deleting from the list then you could use an Excel table (Insert ribbon tab -> Table -> My table has headers), as I have done below. Again, the formula in the Sorted column should be array-entered & it will fill down the column. Then in the Data validation cell(s) use the DV formula shown. The DV list will automatically expand or shrink as you do the same to the table.

    Alphabetical

    HIJKL
    1NameDataSorted
    2dogData 1cat horse
    3catData 2dog
    4mouseData 3horse
    5horseData 4mouse
    6

    Spreadsheet Formulas
    CellFormula
    J2{=INDEX([Name],MATCH(ROW()-ROW(Table1[#Headers]),COUNTIF([Name],"<"&[Name])+1,0))}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!

    Data Validation in Spreadsheet
    CellAllowDatasInput 1Input 2
    L2List =INDIRECT("Table1[Sorted]")


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Aug 24th, 2019 at 01:56 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    Board Regular
    Join Date
    Dec 2013
    Posts
    102
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    Can you please try my Macro
    Code:
    Option Explicit
    Sub Salim_Data_Val()
    Dim D As Worksheet
    Set D = Sheets("Sheet1") 'Put here your sheet's name
    Dim i#: i = 2
    Dim arr
    Dim Laste_row#
    Laste_row = D.Cells(Rows.Count, "A").End(3).Row
    Dim rg As Object
    Set rg = CreateObject("System.Collections.Arraylist")
    With rg
     Do Until i > Laste_row
      If Not .Contains(UCase(D.Range("A" & i).Value)) _
       And D.Range("A" & i) <> vbNullString Then _
      .Add UCase(D.Range("A" & i).Value)
     i = i + 1
     Loop
     .Sort
     arr = Join(.Toarray, ",")
     End With
     
     With D.Range("C1").Validation
     .Delete
     .Add xlValidateList, Formula1:=arr
      End With
     Set rg = Nothing: Set D = Nothing
    
    End Sub
    A B C
    1 Name A
    2 dog
    3 mouse
    4 horse
    5 B
    6 A
    7 z
    8 T
    9 cat
    Sheet1

    Worksheet Formulas
    Cell Formula
    A1 Name
    B1
    C1 A
    A2 dog
    B2
    C2
    A3 mouse
    B3
    C3
    A4 horse
    B4
    C4
    A5 B
    B5
    C5
    A6 A
    B6
    C6
    A7 z
    B7
    C7
    A8 T
    B8
    C8
    A9 cat
    B9
    C9
    Last edited by salim hasan; Aug 24th, 2019 at 02:29 AM.

  5. #5
    Board Regular albertc30's Avatar
    Join Date
    May 2012
    Location
    Norfolk
    Posts
    969
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    Quote Originally Posted by Michael M View Post
    What are you actually trying to do ?
    A bit more info about the problem may help, not what you think might be the solution ?
    Outch!

    But you're right, my apologies, it was very late when I posted the post.

    It's basically a sheet where it contains customers and initially I had an array that was being used as data validation to show the customer's list on a cell.

    This off course shows the data as it is and not in alphabetical order.

    As I do not want to filter the data on the customer's sheet, I will now as a solution, have the customer's names column copied to another sheet (Data) and will have that info there being alphabetically sorted.

    Another thing I notice on the cell where it shows the list is that 1)when you click the dropdown the list is very long showing much more than just the customers available in the list, too much just white (no data) and it's wide like twice what it should be.

    Many thanks for your time, and the rest of you that have given input.

    I shall come back to you all today at some point.

    Much appreciated for your time and off course, for helping.

    Cheers.
    Many thanks for all the help, much appreciated.

    Regards,
    Albert

    Reminder
    Remember to use code tags people.

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,390
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    Quote Originally Posted by albertc30 View Post
    Another thing I notice on the cell where it shows the list is that 1)when you click the dropdown the list is very long showing much more than just the customers available in the list, too much just white (no data) and it's wide like twice what it should be.
    Does this comment relate to any of the suggestions made in this thread? If so, which one(s) & how does it relate to those posts?

    Otherwise
    1. Exactly where is your DV list?
    2. How did you derive the list?
    3. What is the actual Data Validation set-up in the cell?


    @ salim hasan
    Can you please look at the options like 'First Cell In Each Column' or 'User Defined Selection' etc when using the HTML Maker so you don't get those long lists of similar formulas or blanks like have been appearing in many of your posts.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,587
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    Hello,

    Among the possibilities, a simple UserForm could help you out ...

  8. #8
    Board Regular
    Join Date
    Dec 2013
    Posts
    102
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    Quote Originally Posted by Peter_SSs View Post
    Does this comment relate to any of the suggestions made in this thread? If so, which one(s) & how does it relate to those posts?

    Otherwise
    1. Exactly where is your DV list?
    2. How did you derive the list?
    3. What is the actual Data Validation set-up in the cell?


    @ salim hasan
    Can you please look at the options like 'First Cell In Each Column' or 'User Defined Selection' etc when using the HTML Maker so you don't get those long lists of similar formulas or blanks like have been appearing in many of your posts.
    I wanted to attach a picture of the file from my PC
    But I don't know how to do this
    please explain
    Last edited by salim hasan; Aug 24th, 2019 at 05:27 AM.

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,390
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    Quote Originally Posted by salim hasan View Post
    I wanted to attach a picture of the file from my PC
    But I don't know how to do this
    please explain
    You cannot "attach" any files in the forum and you cannot show images stored on your computer. You can upload images to a file-share site or OneDrive, Google Drive etc and share a link. However, it is your spreadsheets samples that I am talking about. In general they are better than images because other people can copy them and paste data into their worksheets to test with. The issue I have is the part in post 4 in this thread that is below the heading "Worksheet Formulas". For a start there are no formulas, so we did not need to see any of that section. It is also a long section, making your post and the thread harder to read & navigate.

    Another example is this post of yours. In the 'Worksheet Formulas' section we only needed to see the formula for D2 since the D3:D9 formula are the same (only referring to their own row). When you select the range on your sheet and go to the HTML maker menu, try selecting one of the options I mentioned before.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    Board Regular
    Join Date
    Nov 2013
    Posts
    627
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    One example.
    Name list derived from formula is in N4:N12. Some cells say N8: N12 are blank. Formula is below for Data Validation.
    In the drop down list you will see only names in N4:N7 (Cells having names). All cells are having formula in N4:N12.

    =OFFSET($N$4,0,0,COUNTIF($N$4:$N$12,"?*"))

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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