Excel And Function Broken.

[SebastianHuang]

Can anyone help me with this situation?


So I am comparing rows of data from two spreadsheets. Most of them will return true when I use the function =and(A1:F1=A1:F1). However some of them will return false when they are in fact the same. I suspect it is because I have a couple thousand lines of item I have to compare. When I copy and paste the false columns into another spreadsheet, and ran the test again, they all returned true. Is Excel broken in this sense? Thank you.

 

[6StringJazzer]

Can you describe what you want that formula to do? The formula you showed here does not compare data from two worksheets. In fact it will always return a #VALUE! error, and if entered as an array formula it will always return TRUE.

Do you mean something like
=AND(Sheet1!A1:F1=Sheet2!A1:F1)
entered as an array formula?

(Although Excel has a couple of bugs, there are no functions that are broken.)

 

[SebastianHuang]

Can you describe what you want that formula to do? The formula you showed here does not compare data from two worksheets. In fact it will always return a #VALUE! error, and if entered as an array formula it will always return TRUE.

Do you mean something like
=AND(Sheet1!A1:F1=Sheet2!A1:F1)
entered as an array formula?

(Although Excel has a couple of bugs, there are no functions that are broken.)

Yes entered as an Array.

 

[SebastianHuang]

Solved it.. I was sorting with a column that is not unique lol.