Deleting rows with condition

[RdAdr]

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.

[AliGW]

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

[RdAdr]

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.

[RdAdr]

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.

[oeldere]

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

[RdAdr]

The solution is:

Please Login or Register  to view this content.

And it belongs to the user MickG from the forum mrexcel.com

[CAABYYC]

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

[RdAdr]

Thanks a lot. Very much appreciated.

But I really do not understand your method. I can not make it work.