Searchable dropdown list

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
478
Office Version
  1. 2019
Platform
  1. Windows
Hi, I have created a dynamic searchable drop down list with around 1600 names and it's working perfect except for one thing. I have 2 names in the list "Rajesh" and "Rajesh Shah". Only one name appears in the drop down list when I type "rajesh". What could be the problem,?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
What happens if you type Rajesh Shah? The drow down is going to return the matched text string.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
478
Office Version
  1. 2019
Platform
  1. Windows
What happens if you type Rajesh Shah?
I can select Rajesh Shah but only Rajesh is not visible. If I type only R then both are visible, but I have to search for both in the long list of 100 R's. Is Is not only this all other names which have common beginning word say Fashion and Fashion New then too it is the same problem
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I can select Rajesh Shah but only Rajesh is not visible. If I type only R then both are visible, but I have to search for both in the long list of 100 R's. Is Is not only this all other names which have common beginning word say Fashion and Fashion New then too it is the same problem
That is how the software works, I suppose. I don't believe I can help you with this.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
478
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

That is how the software works, I suppose. I don't believe I can help you with this.
Thanks for the reply. Maybe someone out there will be there to find a solution and help me to solve it.🤞
 

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
558
Office Version
  1. 365
Platform
  1. Windows
I've done something similar using table formatting, conditional formatting, and filter. It won't return a single answer but will narrow down the table.
Pick the cell for your search term partial or otherwise. Select cells to search, conditional format with new formula,
=ISNUMBER(SEARCH(searchtermcell,celltosearch))
Conditional formatting has to be setup carefully. If C3:C1000 is your name list and C1 was your searchtermcell it would look like this when all cells to search are highlighted
=ISNUMBER(SEARCH($C$1,$C3))
After entering search term, filter table by color.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
478
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I've done something similar using table formatting, conditional formatting, and filter. It won't return a single answer but will narrow down the table.
Pick the cell for your search term partial or otherwise. Select cells to search, conditional format with new formula,
=ISNUMBER(SEARCH(searchtermcell,celltosearch))
Conditional formatting has to be setup carefully. If C3:C1000 is your name list and C1 was your searchtermcell it would look like this when all cells to search are highlighted
=ISNUMBER(SEARCH($C$1,$C3))
After entering search term, filter table by color.
This are the formulas in my cell. Please advice what to do and in which formula?
=IF(ISNUMBER(SEARCH(CELL("contents"),F3)),MAX($E$1:E1)+1,0)
=IFERROR(VLOOKUP(ROWS($H$2:H2),LedgerMasters,2,0),"")
=OFFSET(H2,,,COUNTIF($H$2:$H$1472,"?*"))

All the names with 2 words in the table, of which the first word is common, only one name appears in the drop-down list.

Say I have 2 names in the list – 1. Reliance and 2. Reliance New,

If I type R both the names are visible in the search list below. If I type Reliance only one name is visible i.e., Reliance New and Reliance is not visible.

As the list is too long searching a name from a single alphabet will be tedious.
 

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
558
Office Version
  1. 365
Platform
  1. Windows
Maybe a "Helper" column that removes spaces? I think you're asking about changing something in the way the EXCEL engine works which isn't an option.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
478
Office Version
  1. 2019
Platform
  1. Windows
All the names with the name Rajesh is not displayed in the helper column. I hope this image helps to solve this.
Untitled.png
 

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
340
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Try to change this formula:

=OFFSET(H2,,,COUNTIF($H$2:$H$1472,"?*"))
to this
=OFFSET(H2,,,COUNTIF(Sheet1!$H$2:$H$1472,"*?"),1)
Change Sheet1 with name of the sheet
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,195
Messages
5,640,793
Members
417,166
Latest member
Funwayo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top