Page 1 of 2 12 LastLast
Results 1 to 10 of 14

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

  1. #1
    Board Regular
    Join Date
    Jan 2018
    Location
    Kentucky
    Posts
    179
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  2. #2
    Board Regular
    Join Date
    Jan 2018
    Location
    Kentucky
    Posts
    179
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  3. #3
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

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

    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]

  4. #4
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,352
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default 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
    ​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.


  5. #5
    Board Regular
    Join Date
    Jan 2018
    Location
    Kentucky
    Posts
    179
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  6. #6
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    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

  7. #7
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

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

    Quote Originally Posted by Nanaia View Post
    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
    ACADAEAFAGAHAIAJAKALAMANAOAPAQ
    3FALSE00

    Sheet1



    Worksheet Formulas
    CellFormula
    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



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

    Sheet1



    Worksheet Formulas
    CellFormula
    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]

  8. #8
    Board Regular
    Join Date
    Jan 2018
    Location
    Kentucky
    Posts
    179
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Gerald Higgins View Post
    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.

  9. #9
    Board Regular
    Join Date
    Jan 2018
    Location
    Kentucky
    Posts
    179
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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"

  10. #10
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

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

    Quote Originally Posted by Nanaia View Post
    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
    [code]your code[/code]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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