Dropdown list with sorting in another column

[Vincent88]

Hi, Currently I am using formules to make a dropdown list with sorting. How to create VBA code of a dropdown list with sorting function.

AgentProposal_Roster0728_1004.xlsm
	L	M	N	O	P
1	Agent	SortedAgent	Search	Frequency	FinalList
2	Cat G	Bady B	1	1	Bady B
3	Mary K	Cat G	1	2	Cat G
4	John G	Jack S	1	3	Jack S
5	Ken C	Jeffrey L	1	4	Jeffrey L
6	Zita V	John G	1	5	John G
7	Larry Q	Ken C	1	6	Ken C
8	Mandy H	Larry Q	1	7	Larry Q
9	Warus O	Mandy H	1	8	Mandy H
10	Jack S	Mary K	1	9	Mary K
11	Nacy L	Nacy L	1	10	Nacy L
12	Peter B	Peter B	1	11	Peter B
13	Bady B	Queen M	1	12	Queen M
14	Jeffrey L	Robert Y	1	13	Robert Y
15	Queen M	Sussie M	1	14	Sussie M
16	Robert Y	Warus O	1	15	Warus O
17	Sussie M	Zita V	1	16	Zita V
Data

Cell Formulas
Range	Formula
M2:M17	M2	=INDEX([Agent],MATCH(ROWS($L$2:L2),COUNTIF([Agent],"<="&[Agent]),0))
N2:N17	N2	=IF(ISNUMBER(SEARCH(CELL("contents"),[SortedAgent])),1,"")
O2:O17	O2	=IF(N2=1,COUNTIF($N$2:N2,1),"")
P2:P17	P2	=IFERROR(INDEX([SortedAgent],MATCH(ROWS($P$2:P2),[Frequency],0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
'202108'!HelpAgent	=OFFSET(Data!$P$2,,,COUNTIF(Table1[FinalList],"?*"))	P2:P17
'202109'!HelpAgent	=OFFSET(Data!$P$2,,,COUNTIF(Table1[FinalList],"?*"))	P2:P17
'202110'!HelpAgent	=OFFSET(Data!$P$2,,,COUNTIF(Table1[FinalList],"?*"))	P2:P17
'202111'!HelpAgent	=OFFSET(Data!$P$2,,,COUNTIF(Table1[FinalList],"?*"))	P2:P17
'202112'!HelpAgent	=OFFSET(Data!$P$2,,,COUNTIF(Table1[FinalList],"?*"))	P2:P17
'202201'!HelpAgent	=OFFSET(Data!$P$2,,,COUNTIF(Table1[FinalList],"?*"))	P2:P17
'202202'!HelpAgent	=OFFSET(Data!$P$2,,,COUNTIF(Table1[FinalList],"?*"))	P2:P17
'202203'!HelpAgent	=OFFSET(Data!$P$2,,,COUNTIF(Table1[FinalList],"?*"))	P2:P17
HelpAgent	=OFFSET(Data!$P$2,,,COUNTIF(Table1[FinalList],"?*"))	P2:P17

Cells with Data Validation
Cell	Allow	Criteria
O2:O17	Any value
L2:L17	Custom	=COUNTIF($L:$L,L2)=1

 

[Akuini]

How to create VBA code of a dropdown list with sorting function.

If you're interested, here's an example of a searchable drop-down with unique, sorted & non blank list.

Macro to create searchable data validation+combobox

Excel doesn't provide a built-in way to search data validation list. So it could be hard to scroll through the list when the list is long. This searchable data validation+combobox is a way to deal with that problem. It has these behavior: 1. The...

www.mrexcel.com

OR

use a free add-in called "Search deList"

I created an Excel add-in called “Search deList”, to create searchable data validation

I created an add-in called “Search deList”, and I share it as a freeware. Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a...

www.mrexcel.com