+ Reply to Thread
Results 1 to 11 of 11

IF function (IF False skip to next row)

  1. 10-13-2016, 03:24 AM #1
    FatmaMS
    FatmaMS is offline
    Registered User
    Join Date
    10-13-2016
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    2

    IF function (IF False skip to next row)

    As you can see in the template, in my original table I have many paths, some values (current) are repeated.. I wrote a code to find duplicates and uniques (For the sake of simplicity I didn't post the code of that)

    I want to have another table where I only show the "Unique" values (current), such that for example If E2="Unique" paste the value of D2, else search in the next rows and paste the next unique value.

    I hope my logic is clear.

    Regards
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-13-2016, 03:36 AM #2
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: IF function (IF False skip to next row)

    Hi
    Use this formula and copy down and forward
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 10-13-2016, 03:42 AM #3
    soledad
    soledad is offline
    Forum Contributor
    Join Date
    11-21-2013
    Location
    zimbabwe
    MS-Off Ver
    Excel 2003
    Posts
    124

    Re: IF function (IF False skip to next row)

    Here is a way you can do
    Copy range B2:B19 then paste into cell G7
    Select range G7:G24 then Click the Data menu, next Click Remove Duplicates on Ribbon
    Reply With QuoteRegister To Reply
  4. 10-13-2016, 04:12 AM #4
    tamthat
    tamthat is offline
    Forum Contributor
    Join Date
    11-21-2015
    Location
    Philippines
    MS-Off Ver
    Microsoft Office 2016
    Posts
    146

    Re: IF function (IF False skip to next row)

    FatmaMS,

    Get file here!
    Attached Files Attached Files
    Last edited by jeffreybrown; 10-13-2016 at 07:36 AM. Reason: Please limit the quoting of long posts
    Reply With QuoteRegister To Reply
  5. 10-13-2016, 06:30 AM #5
    FatmaMS
    FatmaMS is offline
    Registered User
    Join Date
    10-13-2016
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    2

    Re: IF function (IF False skip to next row)

    Quote Originally Posted by tamthat View Post
    Get file here!
    Thank you so much, however, I tried this with more rows (or data) and it didnt exactly work..
    Can i understand the significant of (1000000-ROW($2:$19), will I have to change it for more data?
    Reply With QuoteRegister To Reply
  6. 10-13-2016, 07:18 AM #6
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: IF function (IF False skip to next row)

    Hi
    Sorry. My formula in post #2 has a mistake. Use these instead the other
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    See the file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 10-13-2016, 07:31 AM #7
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF function (IF False skip to next row)

    Try this...

    This array formula** entered in F7:

    =IFERROR(INDEX(A:A,SMALL(IF(C$2:C$19="Unique",ROW(C$2:C$19)),ROWS(F$7:F7))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    This formula entered in G7:

    =IF(F7="","",VLOOKUP(F7,A$2:B$19,2,0))

    Select F7:G7 and copy down until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  8. 10-13-2016, 07:32 AM #8
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF function (IF False skip to next row)

    Quote Originally Posted by tamthat View Post
    Get file here!
    Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    Reply With QuoteRegister To Reply
  9. 10-13-2016, 07:45 AM #9
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF function (IF False skip to next row)

    P.S.

    You might have to replace the commas with semi-colons.
    Reply With QuoteRegister To Reply
  10. 10-13-2016, 09:28 AM #10
    tamthat
    tamthat is offline
    Forum Contributor
    Join Date
    11-21-2015
    Location
    Philippines
    MS-Off Ver
    Microsoft Office 2016
    Posts
    146

    Re: IF function (IF False skip to next row)

    Quote Originally Posted by Tony Valko View Post
    Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    Ok, i see. I would be more thoughtful next time.
    Reply With QuoteRegister To Reply
  11. 10-13-2016, 09:29 AM #11
    tamthat
    tamthat is offline
    Forum Contributor
    Join Date
    11-21-2015
    Location
    Philippines
    MS-Off Ver
    Microsoft Office 2016
    Posts
    146

    Re: IF function (IF False skip to next row)

    Quote Originally Posted by FatmaMS View Post
    Thank you so much, however, I tried this with more rows (or data) and it didnt exactly work..
    Can i understand the significant of (1000000-ROW($2:$19), will I have to change it for more data?
    1000000 or how many is up to you, but that number must be greater than 19 in $ 2: $ 19.
    It didn't exactly work because "ROW()-6". 6 is mind row($F$6). F6 is cell before cell active the formula!
    the purpose of this is to take the value of ROW()-6 = 1
    Last edited by tamthat; 10-13-2016 at 09:41 AM.
    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. [SOLVED] IF FALSE, skip to the next row
    By MYazal in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-03-2016, 06:28 PM
  2. [SOLVED] If statement is false skip to next row
    By jmack549 in forum Excel General
    Replies: 13
    Last Post: 04-16-2015, 07:32 AM
  3. If statement is false skip to next row
    By jmmaldonadobruin in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-25-2014, 03:04 PM
  4. [SOLVED] Skip false rows in If function
    By stevot38 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-13-2013, 04:58 PM
  5. Skip False Statement in If And Function
    By peterpan1279 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-05-2013, 09:31 PM
  6. [SOLVED] How do I Skip a Row if the Value is False
    By coconnor in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-28-2012, 10:26 AM
  7. [SOLVED] If false skip to next
    By irresistible007 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-17-2005, 07:10 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