IF any of 10 columns equals any of 4 other columns

[Nanaia]

What is the must efficient formula to create a TRUE/FALSE result for the following without having to write a huge step by step OR formula - or is that my only choice?

IF AQ3=AD3, TRUE or
IF AQ3=AE3, TRUE or
IF AQ3=AF3, TRUE or
IF AQ3=AG3, TRUE or
IF AS3=AD3, TRUE or
IF AS3=AE3, TRUE or
IF AS3=AF3, TRUE or
IF AS3=AG3, TRUE or
IF AU3=AD3, TRUE or
IF AU3=AE3, TRUE or
IF AU3=AF3, TRUE or
IF AU3=AG3, TRUE or
IF AW3=AD3, TRUE or
IF AW3=AE3, TRUE or
IF AW3=AF3, TRUE or
IF AW3=AG3, TRUE or
IF AY3=AD3, TRUE or
IF AY3=AE3, TRUE or
IF AY3=AF3, TRUE or
IF AY3=AG3, TRUE or
IF BA3=AD3, TRUE or
IF BA3=AE3, TRUE or
IF BA3=AF3, TRUE or
IF BA3=AG3, TRUE or
IF BC3=AD3, TRUE or
IF BC3=AE3, TRUE or
IF BC3=AF3, TRUE or
IF BC3=AG3, TRUE or
IF BE3=AD3, TRUE or
IF BE3=AE3, TRUE or
IF BE3=AF3, TRUE or
IF BE3=AG3, TRUE or
IF BG3=AD3, TRUE or
IF BG3=AE3, TRUE or
IF BG3=AF3, TRUE or
IF BG3=AG3, TRUE or
IF BI3=AD3, TRUE or
IF BI3=AE3, TRUE or
IF BI3=AF3, TRUE or
IF BI3=AG3, TRUE
If none of these are true then false

 

[Nanaia]

I just realized I need to add that this needs to be checked if any of the above mentioned cells equals more than zero. If both cells equals zero then false.

 

[FormR]

Hi, here is one option you can try:

=SUMPRODUCT(COUNTIFS(AD3:AG3,"<>0",AD3:AG3,CHOOSE({1,2,3,4,5,6,7,8,9},AQ3,AS3,AU3,AW3,AY3,BC3,BE3,BG3,BI3)))>0

 

[igold]

If by "step by step" you mean a macro, then what about this...

Sub TEST()
    Dim i As Long, x As Long
    For i = 43 To 62 Step 2
        For x = 30 To 33
            If Not Cells(3, i).Value = Cells(3, x).Value Then
                MsgBox "False"
                Exit Sub
            End If
        Next
    Next
End Sub

 

[Nanaia]

Maybe a brief explanation of why I'm trying to do this would help. We received CSV's from a different department. These CSV's are used to create a product. They just did a major reorganization of that department. The new folks are putting designs on top of other designs. What I'm trying to do is create a column, or series of columns that double checks the other departments work to make sure a product is not created with two designed that overlap each other. If there is no design (the CSV value is the center of the design) the cell value is zero. If, for example, AQ3 and AD3 both equal zero, I want a FALSE result because I'm looking for interference and two zeros means no interference. If both AQ3 and AD3 equal the same number then I want the result to be TRUE because there is interference.
I hope this helps clarify.

 

[Gerald Higgins]

If I was doing a task like that, I would not try to encode the results of all comparisons into a single formula.
If the formula returns TRUE, you still need to find out which of the possible permutations is driving that result - this formula won't tell you.
Also, if 2 or more permutations generate a TRUE result, the formula won't tell you that.
Perhaps consider using a matrix with all the elements along the top and down the side, and have separate formulas to analyse each permutation, and highlight any match.

 

[FormR]

If, for example, AQ3 and AD3 both equal zero, I want a FALSE result because I'm looking for interference and two zeros means no interference.

Excel 2013/2016
	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
3	FALSE	0													0
Sheet1

Cell Formulas
Range	Formula
AC3	=SUMPRODUCT(COUNTIFS(AD3:AG3,"<>0",AD3:AG3,CHOOSE({1,2,3,4,5,6,7,8,9,10},AQ3,AS3,AU3,AW3,AY3,BA3,BC3,BE3,BG3,BI3)))>0

If both AQ3 and AD3 equal the same number then I want the result to be TRUE because there is interference.

Excel 2013/2016
	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
3	TRUE	5													5
Sheet1

Cell Formulas
Range	Formula
AC3	=SUMPRODUCT(COUNTIFS(AD3:AG3,"<>0",AD3:AG3,CHOOSE({1,2,3,4,5,6,7,8,9,10},AQ3,AS3,AU3,AW3,AY3,BA3,BC3,BE3,BG3,BI3)))>0

I missed a cell reference first time around - but the suggested formula seems to return the results you say you are expecting

 

[Nanaia]

If I was doing a task like that, I would not try to encode the results of all comparisons into a single formula.
If the formula returns TRUE, you still need to find out which of the possible permutations is driving that result - this formula won't tell you.
Also, if 2 or more permutations generate a TRUE result, the formula won't tell you that.
Perhaps consider using a matrix with all the elements along the top and down the side, and have separate formulas to analyse each permutation, and highlight any match.

The only thing I'm trying to get the TRUE result to tell me is that there is an issue so we know to investigate. Our investigation will find the issue and we can kick the CSV back to the other department if it's a major error or fix it ourselves if it's minor.

 

[Nanaia]

Will the same formula apply if the values in the cells are in feet and inches? i.e. 3'-6 3/4"

 

[FormR]

Will the same formula apply if the values in the cells are in feet and inches? i.e. 3'-6 3/4"

Hi, why not try it and let us know