Use of contain filter....help needed

[excel_1317]

i need to map two lists of company names. 1st list is master list having around 1lakh company names. 2nd list have around 500 records which i need to manually match bcoz index and match functions just dont work.

What i currently do is copy a part of company name from list2, go to list1 and apply a contain filter and if any matches are found, take the closest match to list2.

This is a tedious task for 500 companies as it involves to many mouse clicks. If anyone can help me develop a form type of thing. We just need to paste a part of company name in search box and a contain filter is applied to list 1.

Please help me to ease this process as I know many people face same kind of problem at work....

 

[Smitty]

Can you post some examples of each list?

 

[excel_1317]

Can you post some examples of each list?

As I have explained the problem, I have to check company names manually to get 100% result. Use of lookup functions, Index, match etc helps but we cant be 100% sure. Below is sample version of my master datasheet which originally has about 1 lac company names.

Sample of List2 which needs to be compared with master list, i will post in next post.

Excel Workbook
	A
1	Master List
2	24 IP Law
3	A A Thornton & Company
4	A. W. Metz & Co. AG
5	Ab Ovo Patents B.V
6	Abacus Patentanwlte
7	Abel & Imray
8	Ablett & Stebbing
9	ABP Patent Network GmbH
10	Abritt
11	Ackermann Ernst
12	Adamson Jones
13	Advokat Magnus Thorfinn Ab
14	Advokatfirman Carler
15	Advokatfirman Landahl
16	Advokatfirman Smitt
17	Advotec. Patent- und Rechtsanwlte
18	Albright Patents
19	Alexander Ramage Associates
20	Algemeen Octrooi en Merken Bureau
21	Allen & Overy
22	Baudel Delclaux Et Al Cabinet
23	Baudin Chretien Schihin
24	Bauer Hubbuch & Twelmeier
25	Bauer Vorberg Kayser
26	Bauer Wagner Priesmeyer
27	Bauer, Wulf
28	Bauknecht, Gert
29	Baumbach, Dr. Friedrich
30	Baur & Wolhndler
31	BCM Hanby Wallace
32	BDO Stoy Hayward
33	Beachcroft
34	Beachcroft Solicitors
35	Beau de Lomenie
36	Bech-bruun Dragsted
37	Beck & Rssig
38	Beck Greener
39	Beckenstrter & Beckenstrter
40	Becker & Aue
41	Becker & Collegen
42	Becker & Joly
43	Becker & Mller
44	Becker Eberhard
45	Becker Group Europe GmbH
46	Becker Kurig Straus
47	Beckerpatent
48	Beckmann & Norda
49	Beckmann, Dr. Jrgen
50	Beckord & Niedlich
51	Bedarida Dr. Fabrizio
52	Bedekovic, Zeljko
53	Bedell Group
54	Bedwells Solicitors
55	Beecken Rippen Slodowitz
56	Beer & Partner Patentwalte Keg
57	Beer & Partners
58	Beer, Franz
59	Beetz & Partner
60	Begbie Norton & Partners
61	Beglinger Holenstein
62	Behrens, Helmut
63	Behrmann, Niels
64	Beines Lau-Loskill
65	Beisner, Klaus
66	Beiten Burkhardt
67	Beiten Burkhardt
68	Beiten Burkhardt
69	Beiten Burkhardt
70	Beiten Burkhardt
71	Beiten Burkhardt
72	Beja International SA
73	Bell K
74	Belokopytov and Partners
75	Ben-ami & Associates
76	Bender Von Haller Dragsted
77	Bender, Ernst
78	Benech Frederic Cabinet
79	Beneduce Dott.ssa Gianna
80	Benettin Ing. Maurizio
81	Bengt Nihlmark A B
82	Benjamin J Barish & Company
83	Benjon OY
84	Bennett Taylor
85	Bennett Welch & Co
86	Bennigsen-Mackiewicz Edith von
87	Benninger & Eichler-Stahlberg
88	Benno Event Rigging GmbH
89	BENSOUSSAN ALAIN CABINET
90	BENTLEYS STOKES & LOWLESS
91	Benzinger, Kurt
92	Berendt Leyh Hering
93	Beresford & Co
94	BERESFORDS SOLICITORS LLP
95	Beretta Dott.ssa Giovanna
96	Berg, Wilhelm
97	Bergen, Klaus
98	Bergenstrahle & Lindvall Ab
99	Berger
100	Berggren Oy AB
101	Berghaus Ltd
102	Berglunds Patentbyra Ab
103	Berkeley Intellectual Property Services SA
104	Berkenfeld, Helmut
105	Bermans Solicitors
106	Bermejo & Jacobsen Patentes-marcas S.k.
107	Bernard Soyer Conseil
Master List

Excel 2007

 

[excel_1317]

Here is the sample of list 2.......

Excel Workbook
	A
1	List2
2	Ablett & Stebbing
3	ABP Patent Network
4	Atkinson & Company
5	AUDIC
6	August & Debouzy
7	Avocado Rechtsanwlte
8	AXON Patent GmbH, Aesch
9	AYMARD & COUTEL
10	Backstrm & Co
11	Bailey Walsh & Co
12	Baker & McKenzie
13	Br & Karrer
14	Bardehle Pagenberg Dost Altenburg Geissler
15	Barger Piso & Partner
16	Barker Brettell
17	Barlin IP
18	Baron Warren Redfern
19	BECKER & ASSOCIATES
20	Bauer Wagner Priesmeyer
21	Beck Greener
22	Octrooibureau Dokter, (Apeldoorn,Nederland)
23	Downing IP
24	IZI Patents, (Kranenburg,Duitsland)
25	J A Kemp
26	J E Evans-Jackson
27	Jabbusch Siekmann & Wasiljeff
28	JACOBACCI & PARTNERS
29	F R Kelly
30	Jensen & Son
31	Jonas Rechtsanwlte (former Linklaters)
32	Jones Day (DE)
33	Joppich
34	Joshi & Welch
35	Jostarndt Patentanwlte
36	Jungblut & Seuss
37	JURISPATENT DIJON
38	Kahlhfer Neumann Rler Heine
List2

Excel 2007

 

[Smitty]

See: http://www.mrexcel.com/forum/hall-fame-winners/69649-alans-udfs-fuzzy-match-problem.html

 

[excel_1317]

See: http://www.mrexcel.com/forum/hall-fame-winners/69649-alans-udfs-fuzzy-match-problem.html

Hi have seen and used it as well... It is a great UDF but still to be 100% sure i need to do manual matching... Please help me save some time with that..

Many Thanks