# Deleting rows with condition

1. ## 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.

2. ## Re: Deleting rows with condition

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

3. ## 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.

4. ## 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.

5. ## Re: Deleting rows with condition

Please post the solution, for the benefit of the other forummembers

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

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

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

#### 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