Comparing data between files
in [Programming]
|
From: jmenkin on 16 May 2010 14:41 Hi, I have two files that have lists of names, first and last in separate columns. I would like to be able to quickly find out if there are any repeats between the two documents. The names could be in any order, so I don't know how to compare cells, and I'm guessing trying to create a macro to search for the contents in each cell in the 2nd document might be a little slow. Ideas? Thanks!
From: joel on 16 May 2010 15:35 the easy way is to use a sumproduct formula. for eample if you have the first and last names of one workbook in column A & b going to row 1000 and the 2nd set of names in columns C & E. Put in column e =sumproduct(--(C1=A$1:A$1000),(D1=B$1:B$1000)) Then copy formual down column until you reach the last row of column C & d. The non duplicates in column C & D will result in a value of zero. Look at Chip Pearson website under duplicates in a list 'CPearson.com Topic Index' (http://www.cpearson.com/Excel/topic.aspx) -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=203113 http://www.thecodecage.com/forumz
|
Pages: 1 Prev: Page numbering Next: Handling the WorkbookAfterSave Event in VBA |