dropdown list in alphabetical order data validation...

[albertc30]

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

 

[Michael M]

What are you actually trying to do ?
A bit more info about the problem may help, not what you think might be the solution ?

 

[Peter_SSs]

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.

Excel Workbook
	A	B	C
1	dog		cat
2	cat		dog
3	mouse		horse
4	horse		mouse
Alphabetical

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.

Excel Workbook
	H	I	J	K	L
1	Name	Data	Sorted
2	dog	Data 1	cat		horse
3	cat	Data 2	dog
4	mouse	Data 3	horse
5	horse	Data 4	mouse
6
Alphabetical

#VALUE!

 

[salim hasan]

Can you please try my Macro

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
Name
dog
mouse
horse
B
A
z
T
cat

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]A[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A1[/TH]
[TD="align: left"]Name[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]A[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]dog[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]mouse[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A4[/TH]
[TD="align: left"]horse[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A5[/TH]
[TD="align: left"]B[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A6[/TH]
[TD="align: left"]A[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A7[/TH]
[TD="align: left"]z[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B7[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C7[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A8[/TH]
[TD="align: left"]T[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B8[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C8[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A9[/TH]
[TD="align: left"]cat[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B9[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C9[/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[albertc30]

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.

 

[Peter_SSs]

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.

 

[James006]

Hello,

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

 

[salim hasan]

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

 

[Peter_SSs]

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.

 

[kvsrinivasamurthy]

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,"?*"))