Is It Possible to cross match duplicates in 2 different workbooks with a formula?

[Ramenmole]

I have 2 workbooks with various matching data and i would like this to be indicated with "yes" or a "no" in a seperate column in the 2nd workbook, is this possible?

In workbook 1 i have the date in column A and a name in column C, in workbook 2 i have the date in column C and a name in column L, can anyone help me with a formula to check if any of the data in column A and Column C of workbook 1 is the same as column C and Column L in workbook 2?

Any help is much appreciated

[etaf]

yes, are they likely to be duplicates of the data throughout the lists - or will the combination of column A and C be unique ?

perhaps a sample spreadsheets with some dummy data

[Ramenmole]

Thanks for your quick reply, both workbooks have numerous rows of data for every date from August 1st 2012 to present date and i would like to know which dates/names are the same in both workbooks

[Ramenmole]

attach files in 1 workbook

Dummy Book1.xlsx

[etaf]

this should work
Both workbooks need to be open

=IF(ISERROR(MATCH(C2&L2,'[c-book1.xlsx]Sheet1'!$A$1:$A$24&'[c-book1.xlsx]Sheet1'!$C$1:$C$24,0)),"No","Yes")

needs to be entered as an array formula - use - control+shift+enter keys

if you need to refer to a different directory on the PC
'c:\folder\[c-book1.xlsx]Sheet1'!$A$1:$A$24

see attached spreadsheets

note c-book2 column P has the code

[etaf]

crossed post
so i have updated your spreadsheet - i thought you meant two different workbooks not sheets

so modification to formula
=IF(ISERROR(MATCH(C2&L2,Sheet1!$A$1:$A$1700&Sheet1!$C$1:$C$1700,0)),"No","Yes")
enter as array so you get {} around formula

[Ramenmole]

Sorry for the confusion i put them in 1 workbook so you had an idea of what i wanted, but it is 2 different workbooks as per your first formula

[etaf]

cool :up: let me know if it does not work - please check carefully it is working correctly

[Ramenmole]

Sorry etaf i've not explained this well at all, each workbook has a differing amount of rows entered for each date so workbook 1 has 1040 rows and workbook 2 has 2641 rows so the matching data will be in different rows eg. 22/02 danziger is in row 984 in workbook 1 but its in row 2553 in workbook 2, but its a match. Is it possible to add a formula that will find these?

[etaf]

that should not matter
in work book 2 - its comparing all rows in workbook 1
so the code in workbook 2 when the formula is copied down all the rows to 2641 - @row 2553 in workbook2
=IF(ISERROR(MATCH(C2553&L2553,Sheet1!$A$1:$A$1700&Sheet1!$C$1:$C$1700,0)),"No","Yes")
the issue maybe the range here for workbook1
Sheet1!$A$1:$A$1700&Sheet1!$C$1:$C$1700
but should be covered as it goes up to 1700 and you only have 1040 rows

does that make sense ?

your single book example did not go upto row 2553

prince tom is in row 19 sheet1 and also in row 39 sheet 2 and is picked up as Yes (dates the same)

[Ramenmole]

Yes that makes perfect sense, both workbooks are named and have multiple named sheets, do i need to change "sheet1" in the formula to the sheet names that i want checking or just have them open on the correct pages when entering the formula?

[etaf]

do i need to change "sheet1" in the formula to the sheet names that i want checking

yes the range , the sheet name, the filename and file path all need to be correct for your environment

=IF(ISERROR(MATCH(C2&L2,'[c-book1.xlsx]Sheet1'!$A$1:$A$24&'[c-book1.xlsx]Sheet1'!$C$1:$C$24,0)),"No","Yes")

if the works books are in the same folder the above will work
otherwise you also need the full path to the folder they are in

'c:\users\username\documents\folder\[workbookname.ext]sheetname'!rangeofcells

just have them open on the correct pages when entering the formula?

No , the names need to be correct
and both the workbooks need to be open , excel wont look inside a closed workbook without a special function (which I have not been able to work on 2010) on other questions here for some reason.

whats the full path and sheet names of the workbooks you have ?

[Ramenmole]

Thanks for your time etaf, the wokbook paths are:

C:\Users\PINGU\Dropbox\Racing\RTR FRAC LAYS.xlsx]All (workbbook 1)
C:\Users\PINGU\Dropbox\Bst\NARROW LAYS.xlsm]N Lays (workbook 2)

[etaf]

'c:\users\username\documents\folder\[workbookname.ext]sheetname'!rangeofcells

'C:\Users\PINGU\Dropbox\Racing\[RTR FRAC LAYS.xlsx]
is the sheetname All (workbbook 1)
and whats the range name

[Ramenmole]

workbook 1 sheet name is All
workbook 2 sheet name is N Lays

Sorry etaf i don't know what a range name is, could you explain please?

[etaf]

the range of cells you want to look up

1040 rows and workbook 2 has 2641 rows

what columns - A & C and C&L still
C:\Users\PINGU\Dropbox\Racing\RTR FRAC LAYS.xlsx]All

=IF(ISERROR(MATCH(C2&L2,'C:\Users\PINGU\Dropbox\Racing\[RTR FRAC LAYS.xlsx]All'!$A$1:$A$1100&'C:\Users\PINGU\Dropbox\Racing\[RTR FRAC LAYS.xlsx]All'!$C$1:$C$1100,0)),"No","Yes")

into workbook 2 sheet name is N Lays whatever column you want to return the result into - first row and copy down to row 2641

[Ramenmole]

Now that's looking promising, i now have a mixture of "yes" and "no" down the column up to row 2425 in workbook 2 which is the last "yes", from then on its all "no" but i have manually matched some rows below this but the formula has entered "no". I have checked the spelling and date and they both are correct and are a match, any idea what may be causing this?

[etaf]

whats after row 2425 in workbook 2 , in column c and l - thats the matching

[Ramenmole]

row 2553 matches with 984 in book 1
2554 matches with 985
2590 matches with 1024
2595 matches with 1029
2596 matches with 1030
2598 matches with 1034

[etaf]

it should still match if exactly the same
I did not use trim() - so it maybe there is a space after the name - that will not match

can we see those entries at all ?

if you click on the name in 2554 and then click in the formula bar - does the curser stop on the name or is there a space between the name and curser
same for row 985

[Ramenmole]

There's a space in all the errors which i've now deleted and the formula changed to "yes", so i guess i need trim adding to the formula to make sure no other matches are missed?

[etaf]

yes ,

so for the name cells which is C and L - use
TRIM( )

=IF(ISERROR(MATCH(C2&TRIM(L2),'C:\Users\PINGU\Dropbox\Racing\[RTR FRAC LAYS.xlsx]All'!$A$1:$A$1100&TRIM('C:\Users\PINGU\Dropbox\Racing\[RTR FRAC LAYS.xlsx]All'!$C$1:$C$1100),0)),"No","Yes")
should work

dont forget when you edit to add the control+shift+enter to get an array {}

[Ramenmole]

That's done the job, thanks a lot you have saved me from hours of manual checking

[etaf]

your welcome :up: