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

Thread: Satisfy multiple criteria

  1. #1
    New Member
    Join Date
    Nov 2014
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Satisfy multiple criteria

    Hi,

    I have two sets of data, each set has several columns of data. I want to be able to flag any rows that have two numbers that are identical? Here is an example:

    https://imge.to/i/vgKLRV

    I hope that link work let me know if it doesn't.

    I want to flag the right column (M) only if the numbers in column B matches exactly with the number in column K AND the number in column C matches column L. If either dont match, then leave blank.

    PS, is there an easy way to see if the numbers differ by less than 5? This could go in the column N
    Last edited by alanlambden; Sep 9th, 2019 at 08:22 PM.

  2. #2
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Satisfy multiple criteria

    How's this?

    Excel 2010
    ABCDEFGHIJKLMN
    1HoleNorthEastRLRC holeUTM NUTM EDesired ResultDiffer by 5
    2New_19091250725308P_19091250725308New_1
    3New_29091250725358P_29091250725358New_2
    4New_39091250725408P_39091250725408New_3
    5New_49091250725558P_49091250725558New_4
    6New_59091250725608P_59091250725607New_5
    7New_69091300725285P_79091300725353
    8New_79091300725353P_89091300725383
    9New_89091300725450P_99091300725433
    10New_99091350725508P_109091350725258New_9
    11New_109091350725558P_119091350725508

    Sheet18



    Worksheet Formulas
    CellFormula
    M2=IF(AND(B2=K2,C2=L2),A2,"")
    N2=IF(M2<>"","",IF(ABS(C2-L2)<5,A2,""))


  3. #3
    New Member
    Join Date
    Nov 2014
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Satisfy multiple criteria

    This works on a row by row basis, sometimes the numbers will be a match, but on different rows.

  4. #4
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Satisfy multiple criteria

    You'll have to give me an example or two.

  5. #5
    New Member
    Join Date
    Nov 2014
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Satisfy multiple criteria

    Do you see in my image how new_9 is matching identically (B and C = K and L) with P_11, they are on different rows. You code works only if the values match on the same row.

    Im sure we have to use an array or a vlookup but i can quite get it working.

  6. #6
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Satisfy multiple criteria

    It's not making sense to me, unfortunately. B11 matches K10 and K11. The first match is K10. So, since C11 exists somewhere in L (It's in L5), the answer is the first match of B11?

  7. #7
    New Member
    Join Date
    Nov 2014
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Satisfy multiple criteria

    https://imge.to/i/vgWufG

    Hopefully this image makes it a bit clearer. Criteria in both columns must match, but they could be located on different rows

  8. #8
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Satisfy multiple criteria

    I think that made the difference for me. See if this is correct:

    Excel 2010
    ABCDEFGHIJKLM
    1HoleNorthEastRLRC holeUTM NUTM EDesired Result
    2New_19091250725308P_19091250725308New_1
    3New_29091250725358P_29091250725358New_2
    4New_39091250725408P_39091250725408New_3
    5New_49091250725558P_49091250725558New_4
    6New_59091250725608P_59091250725607
    7New_69091300725285P_79091300725353New_7
    8New_79091300725353P_89091300725383
    9New_89091300725450P_99091300725433
    10New_99091350725508P_109091350725258
    11New_109091350725558P_119091350725508New_9

    Sheet18



    Array Formulas
    CellFormula
    M2{=IFERROR(INDEX($A$2:$A$11,MATCH(K2&L2,$B$2:$B$11&$C$2:$C$11,0),1),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself


  9. #9
    New Member
    Join Date
    Nov 2014
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Satisfy multiple criteria

    That worked kweaver!

    Is there a way to adjust it so the numbers aren't an exact match but the numbers differ by 5?

  10. #10
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Satisfy multiple criteria

    I'll look into that when I have some time today.

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
  •