Thread: IF any of 10 columns equals any of 4 other columns Thanks: 0 Likes: 0

1. IF any of 10 columns equals any of 4 other columns

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  Reply With Quote

2. Re: IF any of 10 columns equals any of 4 other columns

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.  Reply With Quote

3. Re: IF any of 10 columns equals any of 4 other columns

Hi, here is one option you can try:  Reply With Quote

4. Re: IF any of 10 columns equals any of 4 other columns

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

Code:
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  Reply With Quote

5. Re: IF any of 10 columns equals any of 4 other columns

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.  Reply With Quote

6. Re: IF any of 10 columns equals any of 4 other columns

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.  Reply With Quote

7. Re: IF any of 10 columns equals any of 4 other columns Originally Posted by Nanaia 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
3FALSE00

Sheet1

Worksheet Formulas
CellFormula Originally Posted by Nanaia If both AQ3 and AD3 equal the same number then I want the result to be TRUE because there is interference.
Excel 2013/2016
3TRUE55

Sheet1

Worksheet Formulas
CellFormula

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

8. Re: IF any of 10 columns equals any of 4 other columns Originally Posted by 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.
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.  Reply With Quote

9. Re: IF any of 10 columns equals any of 4 other columns

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

10. Re: IF any of 10 columns equals any of 4 other columns Originally Posted by Nanaia 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   Reply With Quote

User Tag List

Tags for this Thread

bc3=af3, columns, formula, step, true  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•