Data filtering

[ashani]

Hi team,

I wonder if someone could please guide me on data filtering option :

I have approx 80 telephone numbers and I have a list of data for approx 10,000 numbers - I would like to extract those 80 numbers full line from the list (80 telephone numbers could be there multiple times).

Any formula or VBA syntax would be fine.

Many thanks,

 

[Joe4]

Why not make a copy and use Excel's "Remove Duplicates" functionality to get the 80 unique numbers?

 

[ashani]

is there a formula or easy way to do it as it's a huge database and finding specific numbers in those database. As I said some numbers maybe there multiple times.

 

[Joe4]

It is extremely simple.
See here for instructions: How to Remove Duplicate Entries in Excel

 

[ashani]

Thank you Joe

I think I didn't explain properly, I don't want to remove duplicates - I actually want to find those 80 telephone numbers from the list of 10,000 numbers. Some of those 80 numbers may have been in the list multiple times and I do want them as well.

 

[Joe4]

That is why I said to "make a copy" - so you don't do lose anything from your original list.
If you remove the duplicates from the copy, then you are just left with a list of the unique values.
Isn't that what you want?

If not, then please show us an example of your data and expected output.

 

[ashani]

Hi Joe,

Here's the screenshot - so I would like the numbers to be found in the actual telephone number list.

Thank you!

 

[Joe4]

So, if you copy the telephone number list to that other column, select that new list, and click on the Remove Duplicates, it will do exactly that (just like shown in the link I provided).
What part is tripping you up?

 

[Joe4]

Alternatively, since you are using 365, another option would be to just use the UNIQUE function.
See here: How to use the Excel UNIQUE function | Exceljet

 

[Fluff]

This is my take on this

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	222843	TL071228	Bedfordshire	Luton	Saints		222843		222843	TL071228	Bedfordshire	Luton	Saints
3	180398	SU876803	Berkshire	Windsor and Maidenhead	Boyn Hill		556516		222843	TA084300	East Riding of Yorkshire	Kingston upon Hull, City of	Avenue
4	179942	SU863799	Berkshire	Windsor and Maidenhead	Cox Green		213053		222843	TM034228	Essex	Colchester	Wivenhoe
5	171468	SU678714	Berkshire	West Berkshire	Calcot		185593		213053	TL329130	Hertfordshire	East Hertfordshire	Hertford Castle
6	266957	TL588669	Cambridgeshire	East Cambridgeshire	Burwell		339743		213053	TL232248	Hertfordshire	Stevenage	Old Town
7	519486	NZ427194	County Durham	Stockton-on-Tees	Grangefield		344066		213053	SK598040	Leicestershire	Leicester	Wycliffe
8	530420	NY018304	Cumbria	Allerdale	Seaton		283934		213053	SJ583953	Merseyside	St. Helens	Newton
9	393828	SK018938	Derbyshire	High Peak	Simmondley		339364		344066	SK542440	Nottinghamshire	Nottingham	Bulwell
10	222843	TA084300	East Riding of Yorkshire	Kingston upon Hull, City of	Avenue		395353		339364	SK709393	Nottinghamshire	Rushcliffe	Bingham East
11	393828	TQ316063	East Sussex	Brighton and Hove	Hollingdean and Stanmer				339743	SK012397	Staffordshire	Staffordshire Moorlands	Checkley
12	222843	TM034228	Essex	Colchester	Wivenhoe				556516	NZ352565	Tyne and Wear	Sunderland	St Anne's
13	393828	TQ349861	Greater London	Hackney	Lea Bridge				283934	SP088839	West Midlands	Birmingham	Springfield
14	175927	TQ138759	Greater London	Hounslow	Hounslow Central				185593	SU152855	Wiltshire	Swindon	Gorse Hill and Pinehurst
15	188128	TQ338881	Greater London	Haringey	Seven Sisters
16	121815	SU357218	Hampshire	Test Valley	Cupernham
17	147101	SU362471	Hampshire	Test Valley	Alamein
18	213053	TL329130	Hertfordshire	East Hertfordshire	Hertford Castle
19	213053	TL232248	Hertfordshire	Stevenage	Old Town
20	213053	SK598040	Leicestershire	Leicester	Wycliffe
21	213053	SJ583953	Merseyside	St. Helens	Newton
22	319585	TF642195	Norfolk	King's Lynn and West Norfolk	Fairstead
23	344066	SK542440	Nottinghamshire	Nottingham	Bulwell
24	339364	SK709393	Nottinghamshire	Rushcliffe	Bingham East
25	391570	SK386915	SouTHI	Sheffield	Shiregreen and Brightside
26	324052	SJ937240	Staffordshire	Stafford	Coton
27	339743	SK012397	Staffordshire	Staffordshire Moorlands	Checkley
28	160695	TQ105606	Surrey	Elmbridge	Cobham and Downside
29	556516	NZ352565	Tyne and Wear	Sunderland	St Anne's
30	564168	NZ227641	Tyne and Wear	Newcastle upon Tyne	Elswick
31	283934	SP088839	West Midlands	Birmingham	Springfield
32	303816	SJ993038	West Midlands	Walsall	Bloxwich West
33	111374	TQ184113	West Sussex	Horsham	Steyning
34	434885	SE238348	West Yorkshire	Leeds	Bramley and Stanningley
35	185593	SU152855	Wiltshire	Swindon	Gorse Hill and Pinehurst
Data

Cell Formulas
Range	Formula
I2:M14	I2	=FILTER(A2:E35,ISNUMBER(MATCH(A2:A35,G2:G10,0)))
Dynamic array formulas.