Advanced filter with wildcard

[chef]

Hello all
I am trying all sorts with wildcard and I have not really used advanced filter but am in a muddle trying

I have a dataset in a sheet called staff
column A has Forename Column B has Surname Column C has staff ID no and Column D has dept

I would like in a separate sheet called Interface

Copy the headings over but to be able to use a wildcard in b3 for Surname and then have all displayed below the results from filter

eg if i just typed in "sm" for example or "cr" into b3 the results below would show staff details

with forename/surename/ID/Dept etc for smith/smyth/smithers etc or Crompton/Crieff..depending on letters I put in cell b3

so trying to use autotype to filter out the surnames with what i type in cell b3 then show results from datasets. I can then just click on the staff ID then will take me to another page to display full staff details
I am ok with that, but just looking for help re advanced filter and formula that allows me to type in first few letters as user does not always know full surname plus there could be more than one smith/smithers etc

hope makes sense and any suggestions appreciated

 

[Aladin Akyurek]

Is it possible that you post a 5 row fake sample with headers included and a specification of the output you would want to obtain from that sample?

 

[chef]

Hi Aladin
so..

Forename Surname ID Dept
Joe Smith T1234 Medicine
Brian Smyth T1235 Surgery
Alison Crieff T1236 Surgery
Fiona Smith T1237 Cardio
Derek Dobson T1238 Operations

so if I type the letter s in say cell b3

then the details for smith and smyth would appear in colums below and user could then choose ID no
If I type in Smith then only smith would appear
or if d then dobson would appear

hope makes sense as 14000 staff and would help to filter our choice
regards
ian

 

[Aladin Akyurek]

This can be done either with formulas or with vba that reacts on the choices the user makes. If it's the latter what you are after, someone into vba should, I hope, take this up.

 

[chef]

Hi Aladin
My preference would be to try and do by formula as I'm not great experience with VBA but any suggestions at all by anyone would be greatly appreciated.
Many thanks again for quick reply
regards
ian

 

[Aladin Akyurek]

Book1
	A	B	C	D
1	Forename	Surname	ID	Dept
2	Joe	Smith	T1234	Medicine
3	Brian	Smyth	T1235	Surgery
4	Alison	Crieff	T1236	Surgery
5	Fiona	Smith	T1237	Cardio
6	Derek	Dobson	T1238	Operations
data

Name the range in column A fname via the Name Box or in the Name Manager.

Name the range in column B sname via the Name Box or in the Name Manager.

Name the range in column C id via the Name Box or in the Name Manager.

Name the range in column D dep via the Name Box or in the Name Manager.

Define ivec in the Name Manager as referring to:

=ROW(sname)-ROW(INDEX(sname,1,1))+1

Book1
	A	B	C	D	E	F
2
3		sm	3		select id from below
4		idx	surname	id	T1234
5		1	Smith	T1234
6		2	Smyth	T1235
7		4	Smith	T1237
8
9
10
11
interface

In C3 enter:

=COUNTIFS(data!$B$2:$B$6,$B3&"*")

In B5 enter and copy down:

=IF(ROWS($B$5:B5)>$C$3,"",AGGREGATE(15,6,ivec/ISNUMBER(SEARCH("|"&$B$3,"|"&sname)),ROWS($B$5:B5)))

In C3 enter and copy down:

=IF($B5="","",INDEX(sname,$B5))

In D3 enter and copy down:

=IF($B5="","",INDEX(id,$B5))

Define idlist in the Name Manager as referring to:

=OFFSET(interface!$D$5,0,0,interface!$C$3)

Data validate E4 as List with Source set to:

=idlist

Name E3 as idselection via the Name Box or the Name Manager.

Book1
	A	B	C
1	ID
2	T1234
3	Forename	Surname	Dept
4	Joe	Smith	Medicine
5
filter

In A2 enter:

=idselection

In A4 enter:

=IF($A2="","",VLOOKUP($A2,CHOOSE({1,2},id,fname),2,0))

In B4 enter:

=IF($A2="","",VLOOKUP($A2,CHOOSE({1,2},id,sname),2,0))

In C4 enter:

=IF($A2="","",VLOOKUP($A2,CHOOSE({1,2},id,dep),2,0))

 

[chef]

Aladin,
are you serious!
wow...that worked first time and you are a genius....I have no idea how you could even think to come up with that.

I am so so grateful for your time and help and also just amazed at how you put that soultion together.
Brilliant...

this is going to make users life so much easier to navigate around
Cannot thank you enough and also thank you for step by step instuctions on how to map it all out.

Have a great day
kind regards
ian

 

[Aladin Akyurek]

Ian, you are welcome. And thanks for the kind words.