Searchable Drop down List

[GMC The Macro Man]

Hi guys
I have a drop down list of Pupil Names which i have a formula to to make it searchable as I don't have Office 365 and the new searchable function has not yet been released for Office 2021.
The customer has changed the way in which they supply the data to me, before it was just the FULL NAME but now its 1st Name / Last Name so i have added and extra column (C) to concatenate first and Last..
This works fine but now the searchable part of does not work properly as it sees the formula as data and will either return a Name if there is one there or a blank space.
This formula allows for up to 500 names to be entered on the data page but if only 150 are used then the drop down list would return 350 blank spaces then the 150 names.
I need a way to IGNORE the blanks in the Drop Down List
If you look at the attachment.
Columns A to C is the Data table "PupilNames".
Column E is the searchable list which uses "SORT, FILTER, ISNUMBER & SEARCH"
Column G is the Data Validation Drop Down List.

I hope someone can help
Thanks
George

Book1
	A	B	C	D	E	F	G	H
1	First Name	Surname	Full Name		Sort List		Datalist
2	john	smith	john smith
3	robert	black	robert black
4	thomas	white	thomas white
5	edward	brown	edward brown
6	william	green	william green
7					edward brown
8					john smith
9					robert black
10					thomas white
11					william green
12
Pupil Names

Cell Formulas
Range	Formula
E2:E11	E2	=SORT(FILTER(PupilName[Full Name],ISNUMBER(SEARCH($G$2,PupilName[Full Name])),"not found"))
C2:C11	C2	=A2&" "&B2
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G2	List	=$E$2#

 

[KevCarter]

You are creating a space character in the full name column when it should be blank. Please try:

Book2
	A	B	C	D	E
1	First Name	Surname	Full Name		Sort List
2	john	smith	john smith		edward brown
3	robert	black	robert black		john smith
4	thomas	white	thomas white		robert black
5	edward	brown	edward brown		thomas white
6	william	green	william green		william green
7
8
9
10
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=SORT(FILTER(C2:C10,C2:C10<>"",""))
C2:C10	C2	=IF(A2&B2="","",A2&" "&B2)
Dynamic array formulas.

 

[KevCarter]

Or, probably simpler for column C

:=TRIM(A2&" "&B2)

 

[GMC The Macro Man]

You are creating a space character in the full name column when it should be blank. Please try:

Book2
	A	B	C	D	E
1	First Name	Surname	Full Name		Sort List
2	john	smith	john smith		edward brown
3	robert	black	robert black		john smith
4	thomas	white	thomas white		robert black
5	edward	brown	edward brown		thomas white
6	william	green	william green		william green
7
8
9
10
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=SORT(FILTER(C2:C10,C2:C10<>"",""))
C2:C10	C2	=IF(A2&B2="","",A2&" "&B2)
Dynamic array formulas.

Thanks Kev for taking the time to look at my post. This has worked perfectly

 

[KevCarter]

Happy it helped!