Returning matches for surname and first name or initial

Hi
Can you post few rows of data to make your point clear?
Ravi

m_13,

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Colo's HTML Maker.
http://www.puremis.net/excel/downloads.shtml

Instructions for using "Colo's HTML Maker":
http://www.mrexcel.com/forum/showthread.php?t=89356


Or, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/index.php?f=1

Instructions for using "Excel Jeanie HTML 4":
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Have a great day,
Stan

Thanks for the advice

So I've now managed to get a little further by moving the columns around and getting ARLOOKUP to work. As shown below by using ARLOOKUP all of the asset numbers assigned to somebody with the surname Anderson are shown and below the machines allocated to somebody with the surname Briddon:

Asset Matching


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Helv,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 81px"><COL style="WIDTH: 107px"><COL style="WIDTH: 193px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial">PIN</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial">First Name</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial">Last Name</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial">Machine Name</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">43366</TD><TD>Robert</TD><TD>Anderson</TD><TD style="FONT-FAMILY: Arial">sa006990_488520524153, sa003112_00059a3c7800, sa000160_e09720524153, sa005356_001c25180403</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: right">42445</TD><TD style="FONT-FAMILY: Arial"></TD><TD style="FONT-FAMILY: Arial">Briddon</TD><TD style="FONT-FAMILY: Arial">itn10891_005056c00008, sa007082_500c20524153</TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D2</TD><TD>=ARLOOKUP(RayAsset,C2,"Machine Name")</TD></TR><TR><TD>D3</TD><TD>=ARLOOKUP(RayAsset,C3,"Machine Name")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

But really I want to find the assets of the people who match the firstname and surname.

In the case of Rob Anderson it's these two assets (the other two belong to different Andersons):

Assets from Ray


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Helv,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 127px"><COL style="WIDTH: 145px"><COL style="WIDTH: 217px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD>Anderson</TD><TD>Rob</TD><TD>sa000160_e09720524153</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">35</TD><TD>Anderson</TD><TD>Rob</TD><TD>sa005356_001c25180403</TD></TR></TBODY></TABLE>

However, in the master spreadsheet he is listed as Robert Anderson:

Pool from HR

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Helv,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 54px"><COL style="WIDTH: 158px"><COL style="WIDTH: 158px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>B</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">714</TD><TD style="TEXT-ALIGN: left">43366</TD><TD>Robert</TD><TD>Anderson</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

Others are listed with only an initial followed by a . (for example R. Anderson).

I want to find all the near matches and bring back all of the assets for that near match. Ideally I want to show the matches found too so that I can see where there are problems, ie two variants of the name found.

I then want to shown only the section of the asset number before the _ (If there is one, some are stored cleanly) and separate these so I can use them in a mail merge for labels. Eventually there will be a set of labels per person with one label per asset with the name of the owner and their unique ID.

Hope the screenshots have made things clearer. Great tool!

So I'm still working on this and am now using:

=MATCH(B2&"*",'Assets from Ray'!B:B,0)

Click to expand...

which determined that there are 74 people called Robert or similar in the range (I think).

But then I tried:

=MATCH(C2&"*",'Assets from Ray'!A:A,0)

Click to expand...

which suggests that there are 32 people with surnames of Anderson or similar and that just isn't correct.

Now I'm trying to work out how to join these together to find how many people have a first name like Robert and a surname like Anderson.

Not sure if it's going to help me in my quest though!

I think I'm going to have to give this one up!

I've concatenated the names so I can use Fuzzy Logic but the problem I have now is that a) there are too many people who have data missing on the Asset spreadsheet and b) there are too many anomalies in the names:

Asset Matching

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Helv,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 123px"><COL style="WIDTH: 137px"><COL style="WIDTH: 103px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">37116</TD><TD>S. Routledge</TD><TD>Stuart Routledge</TD><TD style="FONT-FAMILY: Arial">sa005200_001558caafcd</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">37466</TD><TD>Graham Robertson</TD><TD>Graham Robertson</TD><TD style="FONT-FAMILY: Arial">sa005146_00059a3c7800</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">37924</TD><TD>David Higgins</TD><TD>Lynn Davidson</TD><TD style="FONT-FAMILY: Arial">sa005321_001c251633e6</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">39303</TD><TD>Kevin Davies</TD><TD>Carl Davies</TD><TD style="FONT-FAMILY: Arial">sa006609_001a6bd0e0d0</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">42249</TD><TD>C. Hawkings</TD><TD>Colin Hawkings</TD><TD style="FONT-FAMILY: Arial">sa004955_4cef20524153</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">42274</TD><TD>Roger Parkes</TD><TD>Debbie Rogers</TD><TD style="FONT-FAMILY: Arial">sa005038_001279c7b95e</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">42302</TD><TD>K. Burtoft</TD><TD>Keith Burtoft</TD><TD style="FONT-FAMILY: Arial">itn08979_00059a3c7800</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">42436</TD><TD>T. Malster</TD><TD>Anthony Malster</TD><TD style="FONT-FAMILY: Arial">itn09611_e6e120524153</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">42445</TD><TD>Kevin Briddon</TD><TD>#N/A</TD><TD style="FONT-FAMILY: Arial">itn10891_005056c00008</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">42485</TD><TD>James Pugh</TD><TD>chris james</TD><TD style="FONT-FAMILY: Arial">sa002889_00059a3c7800</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">42517</TD><TD>J. Reed</TD><TD>NORMAN SEGREE</TD><TD style="FONT-FAMILY: Arial">itn09774_f2fe20524153</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">42764</TD><TD>M. Carr</TD><TD>Surinder Matharu</TD><TD style="FONT-FAMILY: Arial">sa002883_502f20524153</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">42987</TD><TD>Jayne Webster</TD><TD>#N/A</TD><TD style="FONT-FAMILY: Arial">itn11169_00059a3c7800</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">43535</TD><TD>Paul Morris</TD><TD>Richard Coutts-Morris</TD><TD style="FONT-FAMILY: Arial">itn09805_00059a3c7800</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">764</TD><TD>Dilys Foster</TD><TD>#N/A</TD><TD style="FONT-FAMILY: Arial">sa002999_00059a3c7800</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">823</TD><TD>Mike Clarke</TD><TD>#N/A</TD><TD style="FONT-FAMILY: Arial">sa003938_020054554e01</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">830</TD><TD>Dominic Foster</TD><TD>#N/A</TD><TD style="FONT-FAMILY: Arial">sa002999_00059a3c7800</TD><TD style="FONT-FAMILY: Arial"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">836</TD><TD>D. Troughton</TD><TD>john broughton</TD><TD style="FONT-FAMILY: Arial">sa003560_005056c00008</TD><TD style="FONT-FAMILY: Arial"> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

I've tried having various levels of NFPercent but go too high and I don't pick up the people for whom I have only an initial. Go too low and I always get a match even when the person isn't present.

I'm going to have to improve the data of one part of the sheet to enable me to do this and the names bit from HR seems to be the obvious choice. Particularly as the lack of consistency there is one of my main problems.

Does anybody know if I can nest ARLOOKUP inside FUZZYVLOOKUP so I pick up all the assets that are found for that name?