+ Reply to Thread
Results 1 to 12 of 12

Skip cells and move onto next cell that don't meet criteria using IF fucntion in Excel

  1. 07-06-2014, 06:06 AM #1
    sprewett
    sprewett is offline
    Registered User
    Join Date
    07-06-2014
    Location
    England
    MS-Off Ver
    2007
    Posts
    8

    Skip cells and move onto next cell that don't meet criteria using IF fucntion in Excel

    Hi,
    please can someone help!
    I am trying to use an IF formula in Excel to return a list of everything that meets that criteria and skip those that don't.
    For example using the below table, I want to be able to list all the dates where Mr Smith has the outcome Open in a new worksheet (i.e =IF(AND(A13:A25="Mr Smith",B13:B25="open"),C13:C25,""), how do I return a list with no blanks?
    Hope I've explained this well enough?!

    Name Outcome Date
    Mr Smith Open 01.02.14
    Mr Brown Disqualified 01.02.14
    Mr Brown Qualified 01.02.14
    Mr Barnes Disqualified 01.02.14
    Mr Smith Open 02.02.14
    Mr Brown Open 02.02.14
    Mr Smith Qualified 02.02.14
    Mr Smith Qualified 04.02.14
    Mr Smith Disqualified 04.02.14
    Mr Brown Open 04.02.14
    Mr Alexander Open 06.02.14
    Mr Barnes Qualified 06.02.14

    Thanks!
    Reply With QuoteRegister To Reply
  2. 07-06-2014, 06:57 AM #2
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: Skip cells and move onto next cell that don't meet criteria using IF fucntion in Exce

    welcome to the forum, sprewett. assuming data is:
    Data Range
    A
    B
    C
    D
    E
    13
    Mr Smith
    Open
    01.02.14
    01.02.14
    14
    Mr Brown
    Disqualified
    01.02.14
    02.02.14
    15
    Mr Brown
    Qualified
    01.02.14
    16
    Mr Barnes
    Disqualified
    01.02.14
    17
    Mr Smith
    Open
    02.02.14
    18
    Mr Brown
    Open
    02.02.14
    19
    Mr Smith
    Qualified
    02.02.14
    20
    Mr Smith
    Qualified
    04.02.14
    21
    Mr Smith
    Disqualified
    04.02.14
    22
    Mr Brown
    Open
    04.02.14
    23
    Mr Alexander
    Open
    06.02.14
    24
    Mr Barnes
    Qualified
    06.02.14

    try this array formula in say E13
    =IFERROR(INDEX($C$13:$C$24,SMALL(IF($A$13:$A$24="Mr Smith",IF($B$13:$B$24="Open",ROW($A$13:$A$24))),ROWS(E$13:E13))-ROW($A$13)+1),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
  3. 07-06-2014, 07:00 AM #3
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Skip cells and move onto next cell that don't meet criteria using IF fucntion in Exce

    Use a pivot table for that question instead.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  4. 07-06-2014, 07:06 AM #4
    sprewett
    sprewett is offline
    Registered User
    Join Date
    07-06-2014
    Location
    England
    MS-Off Ver
    2007
    Posts
    8

    Re: Skip cells and move onto next cell that don't meet criteria using IF fucntion in Exce

    Hi benishiryo,

    That seemed to work for E13 but when I copy the formula down it doesn't seem to list the next "Mr Smith Open" straight underneath?
    Am I copying it wrong?
    Any ideas?

    Thanks for your help!
    Reply With QuoteRegister To Reply
  5. 07-06-2014, 07:06 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Skip cells and move onto next cell that don't meet criteria using IF fucntion in Exce

    Benishiryo was too quick!!

    My version needs a helper row, so if his works for you, use it instead...
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  6. 07-06-2014, 07:14 AM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Skip cells and move onto next cell that don't meet criteria using IF fucntion in Exce

    Quote Originally Posted by sprewett View Post
    Hi benishiryo,

    That seemed to work for E13 but when I copy the formula down it doesn't seem to list the next "Mr Smith Open" straight underneath?
    Am I copying it wrong?
    Any ideas?

    Thanks for your help!
    You are not entering it as an ARRAY equation. Rather than hit enter, you must hit CONTROL + SHIFT + ENTER. Then it works perfectly.
    Reply With QuoteRegister To Reply
  7. 07-06-2014, 07:18 AM #7
    sprewett
    sprewett is offline
    Registered User
    Join Date
    07-06-2014
    Location
    England
    MS-Off Ver
    2007
    Posts
    8

    Re: Skip cells and move onto next cell that don't meet criteria using IF fucntion in Exce

    Hi Glenn,
    That was my first thought regarding the array equation but I have double checked an I seem to be getting 01.02.14 in E14? It should say 02.02.14...my formula in E14 reads: {=IFERROR(INDEX($C$13:$C$24,SMALL(IF($A$13:$A$24="Mr Smith",IF($B$13:$B$24="open",ROW($A$13:$A$24))),ROWS(E14:E14))-ROW(A14)+1),"")}

    Is this right?

    Thanks!
    Reply With QuoteRegister To Reply
  8. 07-06-2014, 07:29 AM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Skip cells and move onto next cell that don't meet criteria using IF fucntion in Exce

    You're missing a few anchors...

    ROWS($E14:E14))-ROW($A$14)+
    Reply With QuoteRegister To Reply
  9. 07-06-2014, 07:32 AM #9
    sprewett
    sprewett is offline
    Registered User
    Join Date
    07-06-2014
    Location
    England
    MS-Off Ver
    2007
    Posts
    8

    Re: Skip cells and move onto next cell that don't meet criteria using IF fucntion in Exce

    Fabulous, that works great! Thank you all so much for your help!
    Reply With QuoteRegister To Reply
  10. 07-06-2014, 07:35 AM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Skip cells and move onto next cell that don't meet criteria using IF fucntion in Exce

    If that's it, can you mark the thread as solved and (preferably) say thanks to all who helped by clicking the "Add Reputation" button at the foot of their posts?
    Reply With QuoteRegister To Reply
  11. 07-06-2014, 07:47 AM #11
    sprewett
    sprewett is offline
    Registered User
    Join Date
    07-06-2014
    Location
    England
    MS-Off Ver
    2007
    Posts
    8

    Re: Skip cells and move onto next cell that don't meet criteria using IF fucntion in Exce

    Have done! Thank you
    Reply With QuoteRegister To Reply
  12. 07-06-2014, 07:49 AM #12
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Skip cells and move onto next cell that don't meet criteria using IF fucntion in Exce

    Cheers & you're welcome.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. IF formula - Skip rows that dont meet criteria
    By AusBec in forum Excel General
    Replies: 7
    Last Post: 09-03-2014, 09:52 PM
  2. Skip field if it doesn't meet FIND criteria
    By SWMagic in forum Excel General
    Replies: 3
    Last Post: 04-13-2014, 07:47 PM
  3. Formula to skip a cell if it does not meet criteria
    By viciann in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-07-2013, 12:17 AM
  4. move row data to a new tab when cells meet specific criteria
    By brian807 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 10-10-2012, 10:18 AM
  5. VBA- deleting rows which contain cells that meet criteria, but skip alpha rows
    By cheapkid1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-30-2012, 08:49 AM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1