Postcode searching and sorting

[philb99]

Hi - I have a member database in excel and wish to know how I can search for a number of different Postcodes and produce in a list with the name of the member.

Member Name is in Column F and postcode in in Col K

With UK postcodes they can either start with one letter or two for example B30 or DY10

 

[etaf]

are you still using 2010 version, later version 365 etc , you could use filter , assuming the postcode is entered consistently , but then maybe using * wildcards

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[philb99]

are you still using 2010 version, later version 365 etc , you could use filter , assuming the postcode is entered consistently , but then maybe using * wildcards

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

Thanks for looking - Using Windows 2013

The data is quite simple

Postcodes B1 to B20 and DY1 to DY15 and NP1 to NP17 as examples

 

[philb99]

are you still using 2010 version, later version 365 etc , you could use filter , assuming the postcode is entered consistently , but then maybe using * wildcards

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

Dropbox

www.dropbox.com

 

[etaf]

not on 2013 , I'm still looking into - I suspect using aggregate() would extract a list from list - i have a few old methods , but not that will cover this request
i'll keep looking for a solution , but its been years since i used 2013 and a lot of solutions are now for the later versions 365 etc and would use a FILTER() with a search() and isnumber()
Hopefully some of the more experienced members will reply and provide a solution

heres sheet1 in XL2BB format for members who will not download a file

philb.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	Id	Membership Number	Date Joined	First Name	Surname	Name	Address line1	Address line2	Town	County	Post Code	Mobile No	Landline No	email address	x	xx	xxx	Support Contact Name	Support Group Number
3				Tony	evens	Tony Evans					B1 4XB
4				Steve	brown	Steve Brown					B29 5RF
5				Paul	willis	Paul Willis					DY13 4RF
6				John	stone	John Stone					DY1 5TH
7
8
9
10
11
Sheet1