How to pull every tenth row of information out of a 5,000-row spreadsheet?

[Richard McPike]

At work, we're doing a poll of 500 people, and we've got a list of 5,000
potential people to contact in a vast, 5,000-row Microsoft Excel
spreadsheet.

Is there a way to run a query or a macro or something that will
automatically go in and select every tenth row on this document and
either erase the intervening rows and move all the remaining to the
top, and/or copy the contents of every tenth row into a new document
(but without nine blank rows between each row?)

We're using Microsoft Excel v.X on Mac OS X Jaguar.

Most of what we do is graphic design work. We're all quite fluent in
Illustrator, Photoshop, Quark and InDesign. But Excel is a strange,
foreign language to us.

Someone at another message board pointed me to the VLOOKUP
function, but its some sort of strange code garbage that wants me to
point it to arrays or something. Jesus, I just want to SORT and FILTER a
list. This can't be hard to do. It shouldn't be hard to do, at the very
least. If it is, that's a massive failure of Microsoft's.

All I want is all the data in all the columns of every tenth row of my
spreadsheet. I want to delete all the intervening rows and sort all the
material to the top so that I don't have to scroll through 5,000 lines to
see it all.

Is this possible? If I have to use this BS VLOOKUP thing, what do I put
into its variable spots?

And why isn't there an sort option in Excel that lets me "Show only
every X row starting with row Y."????

If anyone among the teeming millions can help, I would really, really
appreciate it.

 

[Roger Morris]

At work, we're doing a poll of 500 people, and we've got a list of 5,000
potential people to contact in a vast, 5,000-row Microsoft Excel
spreadsheet.

Is there a way to run a query or a macro or something that will
automatically go in and select every tenth row on this document and
either erase the intervening rows and move all the remaining to the
top, and/or copy the contents of every tenth row into a new document
(but without nine blank rows between each row?)

We're using Microsoft Excel v.X on Mac OS X Jaguar.

Most of what we do is graphic design work. We're all quite fluent in
Illustrator, Photoshop, Quark and InDesign. But Excel is a strange,
foreign language to us.

Someone at another message board pointed me to the VLOOKUP
function, but its some sort of strange code garbage that wants me to
point it to arrays or something. Jesus, I just want to SORT and FILTER a
list. This can't be hard to do. It shouldn't be hard to do, at the very
least. If it is, that's a massive failure of Microsoft's.

All I want is all the data in all the columns of every tenth row of my
spreadsheet. I want to delete all the intervening rows and sort all the
material to the top so that I don't have to scroll through 5,000 lines to
see it all.

Is this possible? If I have to use this BS VLOOKUP thing, what do I put
into its variable spots?

And why isn't there an sort option in Excel that lets me "Show only
every X row starting with row Y."????

If anyone among the teeming millions can help, I would really, really
appreciate it.

Here's one way:
in row 1 of a spare column enter the formula =mod(row(Z1),10)

(where "Z" is the spare column - substitute whatever your spare is
called)

replicate this formula down the whole 5000 rows

select and copy the 5000 cells in this same column (only) then without
changing the selection (leave cursor in Z1) do a
Paste Special >- values to get rid of the formulae

Sort on this column (Z) and you will get 10 blocks of 500
You will have to manually delete the unwanted blocks or just copy the
wanted 500 to a new sheet

This is quicker to do than to describe!

 

[J.E. McGimpsey]

At work, we're doing a poll of 500 people, and we've got a list of 5,000
potential people to contact in a vast, 5,000-row Microsoft Excel
spreadsheet.

Is there a way to run a query or a macro or something that will
automatically go in and select every tenth row on this document and
either erase the intervening rows and move all the remaining to the
top, and/or copy the contents of every tenth row into a new document
(but without nine blank rows between each row?)

We're using Microsoft Excel v.X on Mac OS X Jaguar.

Most of what we do is graphic design work. We're all quite fluent in
Illustrator, Photoshop, Quark and InDesign. But Excel is a strange,
foreign language to us.

Someone at another message board pointed me to the VLOOKUP
function, but its some sort of strange code garbage that wants me to
point it to arrays or something. Jesus, I just want to SORT and FILTER a
list. This can't be hard to do. It shouldn't be hard to do, at the very
least. If it is, that's a massive failure of Microsoft's.

All I want is all the data in all the columns of every tenth row of my
spreadsheet. I want to delete all the intervening rows and sort all the
material to the top so that I don't have to scroll through 5,000 lines to
see it all.

Is this possible? If I have to use this BS VLOOKUP thing, what do I put
into its variable spots?

And why isn't there an sort option in Excel that lets me "Show only
every X row starting with row Y."????

If anyone among the teeming millions can help, I would really, really
appreciate it.

Here's a way that's been made for you. It's in the Analysis Toolpak
Add-in. To load that, you need to choose Tools/Add-ins... and check
the Analysis Toolpak Addin checkbox. If you don't see it in the list
of add-ins, you should install it from the Value Pack on your
installation CD (you then need to rerun your updaters -10.1.2,10.1.4
and 10.1.5).

The add-in will add a "Data Analysis" item to your Tools menu.
Select that, then scroll down and select "Sampling", then click OK.
This opens the Sampling form.

Click in the Input Range box and select your range of data (or type
it in). Select the Periodic radio button and enter 10 for the
period. Select the New Worksheet ply (or new workbook) radio button,
then click OK. Every 10th item will be copied to a new worksheet or
workbook.