• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Deleting Random rows

A

amit_gupta123

Member
Hii


I have 500 rows containing data. But i have certain random row numbers viz 2, 25, 30, 82,85, 130...so on. I want data in these row numbers gets deleted and data get arranged without these rows. I am looking forward for a formula instead of VBA code. I tried Index, small, IF, Row and min formula. Got little success but not exactly


Regards
 
Let's say you have a list of the row numbers you DON'T want in col C. Formula to pull data from col A:

=INDEX(A:A,SMALL(IF(ISNA(MATCH(ROW($A$1:$A$1000),C:C,0)),ROW($A$1:$A$1000)),ROW(A1)))


Confirm formula with Ctrl+Shift+Enter, not just Enter.
 
Dear Luke, thks for prompt response but it's working but not exactly the way i am looking for the result. I want the following sample data and the desired result:-

[pre]
Code: 
RefRow	Data	delete row no.	desired result
row 1	 a	   2	           a
row 2	 b	   3	           d
row 3	 c	   5	           f
row 4	 d
row 5	 e
row 6	 f
[/pre]
 
Hi, amit_gupta123!

Would you please elaborate a bit more and post an example of a group of rows that includes some to be deleted and some to be kept?

Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.

Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!
 
Hi amit_gupta123,

I'm not sure what the problem is. When I plug in the formula I posted before, I get the exact output that you just described. Did you confirm the formula with Ctrl+Shift+Enter? Copy the formula downward to get all the results?
 
Hi, amit_gupta123!

Otherwise upload a sample file.

Regards!


@Luke M

Hi!

Don't worry, I'd post anything related in plain text, I know that 2013 didn't even mitigate your CASFFML illness.

Regards!
 
Hi Amit ,


If you read Luke's post once more , you will find out the problem :

[pre]
Code: 
Let's say you have a list of the row numbers you DON'T want in col C. Formula to pull data from col A:
=INDEX(A:A,SMALL(IF(ISNA(MATCH(ROW($A$1:$A$1000),C:C,0)),ROW($A$1:$A$1000)),ROW(A1)))
Luke has posted a formula , explaining that the data to be retrieved is in column A ; if your data is in column B , just change his formula to :

=INDEX(B:B,SMALL(IF(ISNA(MATCH(ROW($A$1:$A$1000),C:C,0)),ROW($A$1:$A$1000)),ROW(A1)))
[/pre]
Narayan
 
Hey Narayan, It's working..thnx a ton.


Luke thks a lot..it's working...my apologies that i didn't explain it properly...u excel ninja's are awesome..hats off...dream to be like you one day...i mean it..thnx
 
No worries amit.

Narayan, thanks for figuring out the missing piece of the puzzle. =)
 
the easiest way to delete randowm rows is to

1) Hold down the Ctrl button

2) close eyse

3) select random rows

4) delete selected rows


EASY AS CAN BE!!
 
Good evening jason


New one on me that way of doing it. Will try it out on Monday......on the payroll spreadsheet.
 
You must log in or register to reply here.
Back
Top