who's got the mad Excel-Fu?

i have a data set that is about 6000 rows x 6 columns.

i would like to pare this down to 600 rows by skipping rows 1-9, 11-19, 21-29, etc. is there a simple way to do this? i'm not interested in highlighting and deleting 600 blocks of data. would be cool if it could take every tenth row and put that data into a separate sheet, or a different area of the same sheet.

So you just need rows 10, 20, 30, etc?

Here's some discussion on deleting every nth row using a VBA macro. It might give you some ideas for VBA code to delete rows.

http://www.ozgrid.com/forum/showthread.php?t=53572

Maybe you could use an if statement to delete any rows not ending in 0.

https://www.youtube.com/watch?v=AJbl2vG0y3M

Add in another column. Put 1 in the first row, get the little "drag cursor" showing and double click it. If it fills up with ones, change the auto-fill options to "fill series" rather than copy cells. You should now have a column counting 1 to 6000.

Now, in an 8th column, put in 10, 20, etc, and complete that to 6000. In the 9th column, put an if statement, if the value in the 8th column is found in the 9th column, return the values for that row from the 9th column. Rinse and repeat.

Gasoline...where did your post go? That was genius!

mtn wrote: Add in another column. Put 1 in the first row, get the little "drag cursor" showing and double click it. If it fills up with ones, change the auto-fill options to "fill series" rather than copy cells. You should now have a column counting 1 to 6000. Now, in an 8th column, put in 10, 20, etc, and complete that to 6000. In the 9th column, put an if statement, if the value in the 8th column is found in the 9th column, return the values for that row from the 9th column. Rinse and repeat.

Try this: =IF(ISNA(VLOOKUP(B1,$A$1:$A$600,1,FALSE)),"",C1)

Where B1 is the 10, 20, 30, etc, and A has the 1-6000, and C is the first row of values. Copy and paste it for D, E, F, G, H, (replacing the "C1" with the right cell) and you'll have all six rows.

4cylndrfury wrote: Gasoline...where did your post go? That was genius!

Agreed, but it won't work since you need it to be in 10's.

Put in a new column with 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Copy that down to the bottom (double click on the drag cursor thing, and have it copy cells). Filter out everything but the 10's.

Yes it was not correct.

mtn wrote:

4cylndrfury wrote: Gasoline...where did your post go? That was genius!

Agreed, but it won't work since you need it to be in 10's. Put in a new column with 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Copy that down to the bottom (double click on the drag cursor thing, and have it copy cells). Filter out everything but the 10's.

a little too manual-laborish for me, but it worked like a charm!

My first step would be "Import into Access or SQL Server"...