Check Row Matches a row in an array

[Cooki]

Hi All

I have 2 workbooks and i want to check if a row matches another row in another book.

Lets say in Workbook1 you have the following

Workbook 1					Workbook2
A	B	C	D		T	Y	U	O
					W	E	R	T
					A	B	C	D

I have the following formula but its not working

{=AND(EXACT(A2:D2,[Workbook1.xlsx]Workbook2!$A$2:$D$4))}

Gives me a result of false

Just want to know if that exact row is within the array.

Can anyone help please

 

[Eric W]

Try:

=MAX(MMULT(--EXACT(A2:D2,'[Workbook1.xlsm]Sheet1'!$A$2:$D$4),{1;1;1;1}))=4

 

[Cooki]

Try:

=MAX(MMULT(--EXACT(A2:D2,'[Workbook1.xlsm]Sheet1'!$A$2:$D$4),{1;1;1;1}))=4

Perfect, never seen MMULT and dont know what that does, so will look into that.

Thank you

 

[Eric W]

MMULT = Matrix Multiplication. Think back to your algebra classes. In this case, it's used to examine every cell in a range, then group the results by row. If you don't care about case, we could use a simpler (albeit longer) formula using COUNTIFS.

Anyway, glad I could help!

 

[Cooki]

MMULT = Matrix Multiplication. Think back to your algebra classes. In this case, it's used to examine every cell in a range, then group the results by row. If you don't care about case, we could use a simpler (albeit longer) formula using COUNTIFS.

Anyway, glad I could help!

Very useful

Just one more thing, to make it complete for what i need, im trying to add indirect into it, but cant get it to work

=MAX(MMULT(--EXACT(A2:D2,INDIRECT("'["&$F$1&"]"&$E$1&"'!$A$2:$D$726),{1;1;1;1}))=4

F1 is the doc name
E1 is the tab name

 

[Eric W]

Two possible issues. First, you need a " after $D$726. Second, INDIRECT doesn't work with closed workbooks.

 

[Cooki]

Two possible issues. First, you need a " after $D$726. Second, INDIRECT doesn't work with closed workbooks.

Dam workbook would be open as well, bu cant get around first issue.

not a problem, formula still works, but means ill have to manually put it into each of the tabs.

Thanks for your help

 

[Cooki]

Two possible issues. First, you need a " after $D$726. Second, INDIRECT doesn't work with closed workbooks.

Hi Eric W

is there a way to return the value of the cell that is incorrect in the forumula instead of false

=MAX(MMULT(--EXACT(A3602:K3602,[MDD_OUTPUT_304.xlsx]valid_mtc_llfc_ssc_pc_combin!$B$2:$L$21411),{1;1;1;1;1;1;1;1;1;1;1}))=11

There will should be only 1 cell that is incorrect

 

[Eric W]

This should just tell you if there is a match. It sounds like what you're looking for is to see if there is a row that matches on 10 out of 11 columns, and if there is, return the value of the mismatched column. What if there are 2 or more rows that match on 10 values? Is it ok to return the column number instead of the value? Do you need to return the row number too? I haven't tried anything yet, pending your answers, but I have to think it will be pretty tough to do what you want.

 

[Cooki]

This should just tell you if there is a match. It sounds like what you're looking for is to see if there is a row that matches on 10 out of 11 columns, and if there is, return the value of the mismatched column. What if there are 2 or more rows that match on 10 values? Is it ok to return the column number instead of the value? Do you need to return the row number too? I haven't tried anything yet, pending your answers, but I have to think it will be pretty tough to do what you want.

Yes it tells me if there is a match within my range in another workbook and gives me a true or false.

Yes if 10 out of the 11 columns match, i need to know what value is incorrect, so saves having to look in the other workbook.

As each formula is unique to the row it would not matter is more then 1 row matches only 10 out of the 11 (think thats what your asking)

The value is needed if possible at all, as there are two work books, so trying to cut out having to look in the other workbook really. I could not think of a way for it to be done, but been asked to look into it anyways.

think ive answered all question, please let me know otherwise

Rich