+ Reply to Thread
Results 1 to 8 of 8

Deleting rows with condition

  1. #1
    Registered User
    Join Date
    10-30-2015
    Location
    Ploiesti
    MS-Off Ver
    2007
    Posts
    6

    Deleting rows with condition

    I have the following columns:

    3 0
    3 0
    3 1
    3 0
    3 1
    4 0
    4 0
    4 1
    4 1
    4 0
    4 0
    5 0
    ...

    For every number in the first column, I want to delete all the rows after the first 1 in the second column.
    So, I must obtain this:

    3 0
    3 0
    3 1
    4 0
    4 0
    4 1
    5 0
    ...

    So I deleted:

    3 0
    3 0
    3 1
    3 0 deleted
    3 1 deleted
    4 0
    4 0
    4 1
    4 1 deleted
    4 0 deleted
    4 0 deleted
    5 0
    ...

    How do I do that? I must do this for a thousand numbers and doing it by hand is very time consuming.

    Thanks a lot in advance.
    Last edited by RdAdr; 10-31-2015 at 04:37 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    53,335

    Re: Deleting rows with condition

    Explain the reasoning behind the numbers you want deleting - it's not obvious what the criterion is.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    10-30-2015
    Location
    Ploiesti
    MS-Off Ver
    2007
    Posts
    6

    Re: Deleting rows with condition

    The reason would not add absolutely anything to the problem (or take away from the problem). There are just the two columns that that I want to delete some rows from them.

    (there are no other columns that could make it more easier)

    Intuitively, there should be a way to program the computer to do this (in pseudocode, I could say: if second column is 1, then delete all until first column changes value, then repeat). But I do not know it.
    Last edited by RdAdr; 10-31-2015 at 05:28 AM.

  4. #4
    Registered User
    Join Date
    10-30-2015
    Location
    Ploiesti
    MS-Off Ver
    2007
    Posts
    6

    Re: Deleting rows with condition

    Or a solution in Access using SQL or VBA, maybe.


    LE:

    The problem got solved.

    I used a macro in Excel posted by some other user.

    Thanks for the answer.
    Last edited by RdAdr; 10-31-2015 at 09:39 AM.

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Deleting rows with condition

    Please post the solution, for the benefit of the other forummembers
    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.

  6. #6
    Registered User
    Join Date
    10-30-2015
    Location
    Ploiesti
    MS-Off Ver
    2007
    Posts
    6

    Re: Deleting rows with condition

    The solution is:

    Please Login or Register  to view this content.
    And it belongs to the user MickG from the forum mrexcel.com
    Last edited by jeffreybrown; 10-31-2015 at 10:23 AM. Reason: Please use code tags when posting code!

  7. #7
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: Deleting rows with condition

    if you want to directly generate the list per your request
    A1=INT((ROW(A3)/3)-1+3)
    B1=IF(COUNTIF($A$1:A1,A1)=3,1,0)
    copy down

    if you want to display the list per your original A and B, following is option by using helper column D, then F and G is what you want

    assume your source data is in A and B
    D1=IF(A1="","",IF(OR(AND(COUNTIF($A$1:A1,A1)<3,B1=0),AND(COUNTIF($A$1:A1,A1)=3,B1=1)),ROW(),"/"))
    F1=IFERROR(INDEX(A:A,SMALL($D:$D,ROW(A1))),"") , copy to G1
    select D1:G1, then copy down

  8. #8
    Registered User
    Join Date
    10-30-2015
    Location
    Ploiesti
    MS-Off Ver
    2007
    Posts
    6

    Re: Deleting rows with condition

    Thanks a lot. Very much appreciated.

    But I really do not understand your method. I can not make it work.
    Last edited by RdAdr; 11-01-2015 at 02:48 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Deleting rows on one sheet based on a condition in another sheet in the same file
    By Steve2107 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-18-2014, 06:48 AM
  2. [SOLVED] Error 13 - Type Mismatch for my macro code for deleting rows based on condition.
    By Whitenoise1 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-19-2012, 07:56 PM
  3. Copy rows from one sheet to another,while deleting from source,under condition
    By piulibero in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-14-2011, 04:35 AM
  4. Replies: 0
    Last Post: 03-29-2011, 03:10 PM
  5. Deleting rows based on condition:help me create a macro
    By chrisjack001 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-08-2010, 07:34 PM
  6. Clearing cells and deleting rows based on condition
    By vijay2482 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-14-2010, 10:08 AM
  7. Deleting rows based on condition
    By Josh_123456 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-10-2008, 04:27 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1