Police enquiry, need to do matches.
Posted by Mo on October 03, 2001 3:36 AM
Hello,
Where I work the police usually send us a list of names, addresses, and phone numbers of individuals for us to look up, i.e. if they are our clients. Usually its a couple of names and I can cope with it. But since the WTC bombing, they have basically sent us entire spreadsheets. The enquiries are just too many for me to cope.
I just wonder is there a way for me to look up the spreadsheet the police sent us, with the spreadsheet of our clients to see if there are any matches.
To make it less complicated, I think it will better to look up the names in one sheet, and names on others. The same for addresses etc. etc. This is just a suggestion, if you got better ideas, or a different angle to look at it, then by all means.
Posted by Eric on October 03, 2001 5:56 AM
You could try using the MATCH function
Hi,
I was thinking along the following lines- of course you'll probably get more targeted answers if you can post specific examples.
Say sheet1 col A,B,C are name, address, telephone number given to you by the police, and sheet2 is your company's information in the same layout. Also assume row 1 is occupied by your column titles.
Keep in mind that the formats need to be exactly the same, that is if they give you a list with parentheses around the area codes, then your list must be the same, likewise sometimes first names vary, or some lists have middle initial and some don't. Even something as innocuous as an extra space in one list can mess this up, so be careful!
In sheet1 D2 type the formula
=match(A2,sheet2!A:A)
this will test for "name" matches
and copy over to F2 and down as far as needed to test for "address" and "telephone number" matches.
If the result is #N/A, then there is no match, if there is a number (it's the row number of the matching value) then there is a match.
You could modify the formula to make the output more readable with
=not(isna(match(A2,sheet2!A:A))) to report "true" for matches and "false" for no matches
HTH
Posted by Aladin Akyurek on October 03, 2001 6:30 AM
Re: You could try using the MATCH function
> If the result is #N/A, then there is no match, if there is a number (it's the row number of the matching value) then there is a match.
Eric --
=ISNUMBER(match(A2,sheet2!A:A))
would be more efficient for that goal.
Aladin
Posted by Eric on October 03, 2001 7:16 AM
Thanks for simplifying that!
Somehow I got it into my head that I needed to detect the #N/A and wanted to avoid using the ISERROR function, all the while missing the just as effective and considerably less convoluted ISNUMBER. I turned left instead of right and never looked back. They must have switched me to decaff!
Thanks for pointing that out!
Posted by Mo on October 03, 2001 9:41 AM
Re: Thankyou both very much
