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.
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
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.
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
[code]your code[/code]
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
igold
I'm a drinker with a coding problem...
All code is written with Excel 2010 - Please test all code on a backup copy of your data.
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.
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 following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !
Sub Macro()
ActiveCell = "IY" & Right(Application.Name, 5)
With ActiveCell.Characters(Start:=2, Length:=1).Font
.Name = "Webdings"
.Color = 255
End With
End Sub
Excel 2013/2016
AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ 3 FALSE 0 0 Sheet1
Worksheet Formulas
Cell 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
Excel 2013/2016
AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ 3 TRUE 5 5 Sheet1
Worksheet Formulas
Cell 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
[code]your code[/code]
Will the same formula apply if the values in the cells are in feet and inches? i.e. 3'-6 3/4"
Like this thread? Share it with others