how to delete every 2nd and 3rd row?

hello, I have some data in a single column, I need to delete every 2nd and 3rd, that is to reduce the amount of data by 2/3. How can I that in office 2010? can you tell me a non-VBA/macro way?

Thanks.

Please Login or Register  to view this content.

The formula in B2 and down is

=IF(MOD(ROWS(B$1:B2)-2,3)=0, ROW(), 0)

Copy col B, replace with values, sort everything by col B, then delete the rows with 0.

I have absolutely no idea.... :' (

if you'd be so kind can you do it for me?

You don't have a header row, so in B1, enter the formula

=IF(MOD(ROW()-1, 3)=0, ROW(), 0)

Copy that all the way down.

Click the Home tab, select col B, click Copy, click Paste > Paste Values

Click the Data tab, select columns A and B, click Sort, select Column B in the dropdown, and press OK.

Then delete all the rows that have a 0 in col B.

I tried my best, this is what I got...

Now just select and delete rows 1:332 and you're done.

are you sure shg?

the data in column B are not my data, they are row numbers, just scroll down and see! And also column B is in ascending order, I need the data to be in the original order because it is time series data.

edit: can you just do it for me, I need this data asap because I have assignment due soon, I promise I will comeback and learn to do it myself after and I will also promise to learn VBA.

If you follow the steps in the exact order I gave you, you have the surviving data in the original order. I'll convince you.

Add the formula in B1

In C1, =ROW()

Copy B1 and C1 down to the end of the data. You'll see this

Please Login or Register  to view this content.

Copy columns B and C and replace with values (looks the same as above, but there are no formulas left.)

Sort everything by col B:

Please Login or Register  to view this content.

Delete all the rows that have a 0 in col B (which is the first 332), leaving

Please Login or Register  to view this content.

Note the pattern of the original row numbers in columns B and C.

Ohhhhhhhhhhhhhhhhhhhhh!!!!!!!

I　actually got it in book2-1, I was slow when you said delete row 1:332. lol

Thanks man you're a legend.

Dawn cometh. Good job.

shg

off topic question, how can I add an avatar?

From the Forum Rules page...

Once you have been registered for 60 days and posted 100 posts your user title will change from Registered User to Forum Contributor, you will be able to upload a non-animated avatar. When you get to 120 days registered and 200 posts, your title will change to Valued Forum Contributor and you will be able to upload an animated avatar. When your title reaches Forum Guru you can upload a profile picture, we will leave you to wonder how long you have to be registered and how many posts for that!.

Dom