Compare my list to an existing list

I have a list of names approximately 250 rows long in column A. For the most 
part, they are in the format of [last_name, first_name + middle_initial]... 
all in one cell. I can modify that, if necessary. Perhaps with a text to 
columns function.

I downloaded a database from the tax department containing rows of data of 
parcel owners. The owners names are contained in the following fashion: their 
last names are in column A, their first and middle initials are contained in 
column B. As I mentioned above, I can get my sheet set up like the first one, 
or the database set up like my list. It would probably make more sense to 
make the database look like my list... you think?

I would like to match my list of names up to the tax database and omit all 
the other rows whose names don't match up to my list. If I had to end up with 
some duplicate matches, I will be okay.

For example, if I have "Williams, Joseph A" in my list and it creates a list 
of 4 matches, such as "Williams, Andrew B", "Williams, Calvin C", "Williams, 
Mike P" and "Williams, Seth W"; I am okay with that. I would rather have some 
non-exact matches, than to omit something I wanted to keep. So, in other 
words, I would at least like to filter out the tax database of any names it 
contains that don't match up with any last name in my list.

I appreciate any help that someone may offer. If you need further 
clarification of what I am trying to accomplish.
0
Utf
12/18/2009 3:06:02 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
1033 Views

Similar Articles

[PageSpeed] 23

One formulas play to tinker with ...

Assume your source 250 names is within A2:A250 in Sheet1
In Sheet1,
Put in B2: =TRIM(LEFT(A2,SEARCH(",",A2)-1))
Copy down. This extracts all the last names into col B 
(It is presumed that there will not be any duplicate last names in Sheet1) 

Assume the tax d/b is in Sheet2, cols A to say, col D, data from row2 down, 
where col A contains last names
In Sheet2
Put in E2, normal ENTER will do:
=IF(A2="","",INDEX(Sheet1!A$2:A$250,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(Sheet1!B$2:B$250,A2)),),0)))
Copy down all the way. Col E will return the source names from Sheet1 (col 
A's names) where the last names match, exactly what you seek to accomplish. 
Voila? hit the YES below 
-- 
Max
Singapore
--- 
"Beep" wrote:
> I have a list of names approximately 250 rows long in column A. For the most 
> part, they are in the format of [last_name, first_name + middle_initial]... 
> all in one cell. I can modify that, if necessary. Perhaps with a text to 
> columns function.
> 
> I downloaded a database from the tax department containing rows of data of 
> parcel owners. The owners names are contained in the following fashion: their 
> last names are in column A, their first and middle initials are contained in 
> column B. As I mentioned above, I can get my sheet set up like the first one, 
> or the database set up like my list. It would probably make more sense to 
> make the database look like my list... you think?
> 
> I would like to match my list of names up to the tax database and omit all 
> the other rows whose names don't match up to my list. If I had to end up with 
> some duplicate matches, I will be okay.
> 
> For example, if I have "Williams, Joseph A" in my list and it creates a list 
> of 4 matches, such as "Williams, Andrew B", "Williams, Calvin C", "Williams, 
> Mike P" and "Williams, Seth W"; I am okay with that. I would rather have some 
> non-exact matches, than to omit something I wanted to keep. So, in other 
> words, I would at least like to filter out the tax database of any names it 
> contains that don't match up with any last name in my list.
> 
> I appreciate any help that someone may offer. If you need further 
> clarification of what I am trying to accomplish.
0
Utf
12/18/2009 6:41:02 AM
Reply:

Similar Artilces:

compare
Hello everyone, I have been reading this group for a while, however I am quite a rookie in using of excel. I would appreciate if someone could help with this issue. I quite often have to compare two sheets (from different workbooks-files, but with the same sheet name). Calculations are thus updated time by time and I need to check where were the main differences. Cells contain both values and formulas. I have found through this newsgroup nice add-ins of Myrna Larson and Bill Manville, and Rob Bruce. However I would need the macros to highlight only significant differences (let's say fro...

Importing a contact list from Excel
I have been trying to import a contact list from Excel into Outlook. It keep telling me that I need to rename my ranges in the Excel document but I cannot find anything that tells me how to accomplish this. ...

MRU File List
Can someone point me to a MRU File List tutorial? -Tom R. "Thomas Rybka" wrote: > Can someone point me to a MRU File List tutorial? > > -Tom R. > I am certain there are better solutions; but the following code worked for me.... // When we create a new document the first time, we will // fill that documents first view with data read from the // first file in the users Most Recently Used (MRU) list of files. (or "Recent File List") // String defined per documentation (Word "File", ending with formating "%d") static cons...

lists #2
Hi I have created a drop down list in excel which is attached to a table (via VLOOKUP). The drop down list contains a code number. What I would like is a comment next to the list saying what each code represents (eg 100 - stationery) but only putting in the value when selected (as it is part of a formula). does anyone know how to do this? any help appreciated. kind regards Rexmann Kind regards Gareth Hi! If this is a listbox from the Control Toolbox then you can do this: Call up the listbox's Properties Select ColumnCount and enter 2 Select ColumnWidths eg 30, 100 (depends on you...

Calendar and task list in same view with Outlook 2003
I used to have a view in Outlook 2000 that had both my calendar and my task list. Is this possible in Outlook 2003? How would you do it? In Outlook 2003 to show the Task List with your calendar you need to go to the Calendar in Outlook and select View -> Task Pad. I hope this information is helpful. Robert Findlay Partner Technical Lead -- BizApps Microsoft Technical Support for Platforms and Business Applications ...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

2 workbooks
I have got 2 workbooks with a list of names. I need to find out if a name appears in both books. One of the workbooks came from a different source and the other one is a report I ran from our database. I need to find out whether the workbook from out of the company has got any of our own names on it. I am using Microsoft Excel 97 and am fairly new at this so please be gentle. One way I did think was to combine the two workbooks into one and find the duplicates but thought there may be another way. Thank you. Hi "queen on", Assume that In book1 the names are in column A, s...

Comparing
I have two columns of numbers column A could be up to 3500 rows. Column B may be more or less than 3500 rows. What I'm trying to do is see if any number in columns "B" appears anywhere in Column "A", and if so we can just highlight it in both columns. Actually I would want to be able to sort by highlighted. so maybe add someway to sort by matched or unmatched. I hope this makes sense. In column C put... =IF(ISNA(VLOOKUP($A1,$B:$B,1,FALSE)),"Not in B","In B") In column D put... =IF(ISNA(VLOOKUP($B1,$A:$A,1,FALSE)),"Not in ...

Custom Smart Lists missing after SP7
It appears that we no longer have any of our custom smart list reports after the upgrade. Is there any way to restore or recover this. Which dictionary do custom smart lists reports get stored? We may have a backup of that dictionary. ------=_NextPart_0001_3EBC268F Content-Type: text/plain Content-Transfer-Encoding: 7bit Hello and thank you for using Newsgroups! Can you clarify the following: Are you referring to Smartlist favorites? These you create within Smartlist and it allows you to specify additional search criteria for smartlist objects. If this is the case, these are stor...

I need to compare to columns and indicate the matches in another
am wanting to compare 2 columns for exact matching. If there are two matching items I want to be able to say "A match" in a chosen cell for all the ones that match. Column C will be retrived in an random order... so how would I write the formula for that????? Example: A B C 45time 11tune A match 11tune 89time 47doog 43jkjkj A match 123ABC 123ABC If possible include how to highlight the ones that make as another option. Thanks Try something like this: For a value list in B1:B5 and a ...

Outlook Folder list #3
I have a customer who's folder list in Outlook 2002 is no longer arranged alphabetically. Does anyone know how to change it back? ...

Advice on comparing data sets
Hi, Can anyone advise on how I can compare data on 1 sheet with dat compared on another. Example attached. I want to be able to show that whenever the UK is shown (can appea multiple times) on this sheet it checks on sheet2 and enter th corresponding band value in this case for the UK (show in cells B6, B8 will show band A SHEET1 Ref Country Band 1 UK 2 Germany 3 UK 4 France 5 Italy SHEET2 Country Band UK A GERMANY B ITALY B FRANCE D SWEDEN D DENMARK E As always thanks for your help. Simo +------------------------------------------------------------------- |Filename: ...

Outlook should let me remove the favorites listing
I find the 'Favorite Folders' window to be a nuisance that has the added advantage of wasting desktop real estate. I'm a new user to Office 2003, having used previous versions for years. Imagine my annoyance when I look in Help, and am told point blank that I can neither move nor remove this useless panel. It's as annoying as the "Personal Menues" feature, but that, at least, can be removed. If you wish to have your issue considered for future releases of Outlook you should send an e-mail with your issue to the MSWISH@Microsoft.com alias and the developers ...

How to obtain Averages from a list of multiple items?
Example:Excel worksheet-Column A has list of Cat or Dog (say 15 items)-Column C has their age in days old (10,3,4,etc) I need (2) Averages- Average age of Cats and Average Age of Dogs Hi! =SUMIF(A1:A50,"cat",C1:C50)/COUNTIF(A1:A50,"cat") Do the same for dog. Biff "MadameJunk" <MadameJunk@discussions.microsoft.com> wrote in message news:B0E6C651-E09D-4FA4-A0BB-F859A88CA81A@microsoft.com... > Example:Excel worksheet-Column A has list of Cat or Dog (say 15 > items)-Column > C has their age in days old (10,3,4,etc) > I need (2) Averages- Aver...

comparing cell
i have a spreadsheet that contain a column of name (some the same and some not) and would like to place each name in a row acroos the top of the same spreadsheet. Smith Jones Brown Smith Smith Brown Smith Jones Jones Smith Brown Hi Stanley First insert a heading above your column of names. Then Data>Filter>Advanced Filter mark the source as your column of names. Choose copy to another location and choose a detination cell on the sheet. Select Unique values only. Having got a unique list of names, copy this list and Paste Special>Transpo...

List Comparison
I would be most grateful if someone could help. I have two lists residing in Column A and Column B respectively. Both lists contain 4 figure numbers. Column A contains the 'complete list' and Column B only contains 'some' of the entries from Column A. I would like Column A compared to Column B and any numbers that are not in Column B but are in Column A put in Column C. Many thanks in advance. hi, in column C enter and then copy formula down =IF(ISERROR(MATCH($A$1:$A$6,$B$1:$B$6,0)),$A$1:$A$6,"") Change range to fit your needs "SiH23&qu...

Smart List and Receivables Payments
HI, When I use the default Receivables Transactions Smart List, the records for payments and returns have $0 values despite the fact that a payment was received. The payment is accurately represented elsewhere in the system (e.g. transactions by Customer), but for whatever reason the payment and return values do not show up anywhere in smart list. Why is this? And how can we fix? Thanks, Jason The problem is the amount column that is displayed by default. You need to select the Document Amount column. "Jason" wrote: > HI, > > When I use the default Receivables Tr...

Compare and delete row
I need some help - I need to compare a cell, let's say A1, that wil contain a date. I need Excel to compare it to today's date, if it i over 60 days, I need it to go ahead and delete that row. If not, I nee it to leave alone. Thanks in advanc -- Message posted from http://www.ExcelForum.com Try this Sub test() If IsDate(Range("a1")) = True Then If Range("a1").Value > Date + 60 Then Range("a1").EntireRow.Delete End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "intheway >" <<intheway.176s7x@excelforum-nospam.c...

Range name applies to list, or simply range?
In Excel 2003, if I go through the pull-down menus to define a range name, I can see the bounding spreadsheet coordinates for the various named ranges. (Don't have access to the machine with Excel at the moment, so sorry if this is a bit vague, but I think the menu traversals might be something like Insert->Name->Define). Is there a way to determine whether the names apply to a simple range, or a list? Currently, my way of testing that is to insert rows into a list and see whether the range coordinates for the list name changes. There must be a more direct way than to muss around ...

Global Address List #3
We have two Windows 2000 AD domains, each with their own Exchange 2000 server. We would like to be able to share the Global Address Lists between the two domains. What is the easiest way to do this Thanks! ...

How to insert a list in the ActiveX combo box
I have inserted ActiveX combo boxes into a form, but cannot figure out how to insert the list of choices that will appear when I click the drop down. I am using Excel 2000. Does anyone know how to do this? I can create form's drop down lists and another form of drop down off the tool bar, but I understand that if I want this data to be communicated with a database I need to use the ActiveX control. -- Carl Snider With the control shown and in design mode, click the [Properties] icon on the Control Toolbox. Look for ListFillRange - that's where you tell it where to get the ...

Mailing list for Exchange 5.5 or 2000(2003)
Is there a way to setup a List serve for exchange using what's included with exchange or do you need a third party program to manage one? Thanks in advance. Chris DeFreitas I'm sure you can script something and if you don't mind allowing automatic replies to the internet (potential for loops). As a last resort: Mailman Majordomo Yahoo Groups is also easy to setup and free if you don't mind some ads. http://groups.yahoo.com/ -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to...

compare side by side
Using WindowsXP-Media Edition with Service Pack 2 and Office 2003. When I choose the "compare side by side" option, my two workbooks appear one above the other rather than side by side. Am I missing something?? >>Am I missing something?? I commented on that to MS during the beta test. They ignored me! -- Jim "BK" <nospam@nospam.com> wrote in message news:%233jhCdasHHA.1216@TK2MSFTNGP04.phx.gbl... | Using WindowsXP-Media Edition with Service Pack 2 and Office 2003. | | When I choose the "compare side by side" option, my two workbooks appear ...

Global Address List Sort
I have taken over a network and have an Exchange 2003 server. The admin before I has somehow sorted the Global Address book with he Groups at the top, but any new group I add just go into alphabetical order with the other users. I have been searching around and can find nothing about this. All the users here are use to this and would like to continue with it. Any help would be great. On 8 Dec 2006 07:24:24 -0800, "Coleen" <coleen33@hotmail.com> wrote: >I have taken over a network and have an Exchange 2003 server. > >The admin before I has somehow sorted the Globa...

Comparing Excel Spreadsheets
Ok, got one I can't handle.. I have one of two scenarios. Either I have 2 Excel workbooks, or 1 workbook with 2 worksheets.. Either way, I have 2 one Column data sets. I need a way to look at the data sets and find what is missing Example Data 1 = 1 2 3 4 5 Data 2 = 1 3 5 6 Results = 2 4 6 Does anyone know how to do this? Thanks Joe ...