Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to print out matching names?
I have two files each holds a long list of names and other personal
information. The names are located in Column A. How can I compare the list of names and ask Excel to print out on Column B of File2 the word "match" if an exact match is found, but nothing if it is not an exact match, like: File1 File2 Down Down match Downes Downer Thank you for your help. Regards, Tom |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to print out matching names?
Hi Tom,
Assuming that the list of names is in Book1 on sheet 1 from A1 down to whatever row and the other list is in Book2 from A1 down to whatever row. Put the following formula on sheet 2 in cell B1. Note: The formula is actually one line but it breaks in half here. =IF(ISERROR(VLOOKUP(A1,'[Book1 vlookup.xlsm]Sheet1'!A$1:A$26,1,FALSE)),"","Match") Copy the formula down column B as far as the bottom of the data in column A. Regards, OssieMac "Tom" wrote: I have two files each holds a long list of names and other personal information. The names are located in Column A. How can I compare the list of names and ask Excel to print out on Column B of File2 the word "match" if an exact match is found, but nothing if it is not an exact match, like: File1 File2 Down Down match Downes Downer Thank you for your help. Regards, Tom |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to print out matching names?
If you don't have column labels at the top of your data, insert rows and
add the column label "Name". Assuming Book1 is the reference list, and Book2 is the data workbook where the formula will be added, sort Book2 by Name first, then enter the following formula in cell B2 and fill down: =IF(A2=A1,B1,IF(ISNA(MATCH(A2,[Book1]Sheet1!$A$2:$A$100,0)),"","Match")) Column B checks to see if the Name in column A is the same one as the row immediately above. If so, then the formula sets the value in column B to the same as the row above. This greatly reduces the number of calls to the MATCH function, thus speeding up recalculation. -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to print out matching names?
My thanks to OssieMac and Bill Renaud. Much appreciate both your help.
Regards, Tom "Tom" wrote in message .. . I have two files each holds a long list of names and other personal information. The names are located in Column A. How can I compare the list of names and ask Excel to print out on Column B of File2 the word "match" if an exact match is found, but nothing if it is not an exact match, like: File1 File2 Down Down match Downes Downer Thank you for your help. Regards, Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching Names | Excel Discussion (Misc queries) | |||
Matching Names | New Users to Excel | |||
Matching names | Excel Discussion (Misc queries) | |||
Matching names in two columns | Excel Discussion (Misc queries) | |||
Sorting and matching rows of names with Socials with master list and eliminating the extra names | Excel Worksheet Functions |