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.
If anyone among the teeming millions can help, I would really, really appreciate it.
[1] Insert a column in the beginning of the worksheet.
[2] Auto-fill (enter first few examples and then drag handle down) numbers 1-5000 or whatever all the way down to the bottom.
[3] In a new sheet, auto fill the numbers 1, 11, 21, 31, etc. all the way to 5001.
[4] Use a Vlookup statement to crosslink the columns of your choice to the original sheet.
[5] When it looks the way you want, you might do a copy and then “paste as values” so the Vlookup formula goes away.
And what do you mean by “drag handle down”? I’ve never in my life been able to figure out how to Auto-Fill in Microsoft Excel. We generally avoid Microsoft software like a plague at my company, but sadly, this is what the University has sent us to work with.
And I’ve found the VLOOKUP function, but I don’t know how to use it. It’s code, blasted, awful, hateful computer code. It’s too much like programming. If I wanted to program, I’d shoot myself in the head.
Gah, why can’t Microsoft design a simple point-and-click interface? Why isn’t there a “filter out all but every X rows starting with row Y” function?
Does anyone know how to use this blasted VLookup function?
All I need is all of the columns from every tenth row, starting with row 1. This should not be hard.
Well, to be fair, what you’re asking for is not exactly the most common procedure. I think VLOOKUP is about as intuitive as it gets for this function.
At this point it might be quicker for you to just do the auto-fill, so all the rows are numbered, and then manually delete any rows that don’t end in 1. Sure it might take an hour, but how long will it take you to learn how to use Excel formulas?
Insert a new column as column A
Auto fill it 1 through 5000 (or whatever the highest number is)
Insert a new column as Column B
In B1 enter: =mod(A1,10) (Note: you must put in the equal sign!)
Double click on the bottom right hand corner of cell B1 - this should fill the entire column with the number 0 through 9 repeating.
Select your entire spreadsheet
From the menu bar -> Data -> Filter -> Auto Filter
Click on the newly selected arrow on column B.
Select 0 or 1 or whatever number suits your fancy.
At this point, you will see only every 10th record.
All the data will be there, just hidden.
To remove all the other data:
Select the entire spreadsheet.
From the menu bar -> Edit -> Go To -> Special -> Visible cells only.
From the menu bar -> Edit -> Copy
Go to a new workseet (Insert -> Worksheet if you need to)
From the menu bar -> Edit -> Paste
The only data you should now see would be the selected records from the first worksheet.
Trust me, this took WAY longer to type than it takes to implement.
I figure doing it manually will take hours. It’s 5,000 rows, selecting nine at a time, then deleting those rows, will take forever. The person who will be doing this won’t be in much tomorrow, and we just got this stupid list from the university today, and these emails must go out on Friday.
I’m only asking because I’m trying to help her out, and because if she doesn’t get the emails out, it messes up a project of mine. But sadly, Microsoft has decided to stand in my way.
I hate Microsoft. God, I hate Microsoft. This should be one-click simple. Just like in a database. “Show every X row starting with row Y” (with an option to do columns instead of rows).
For creating randomized call lists or email lists, this would be a godsend. Of course, Microsoft doesn’t give half a flip about the comfort or productivity or happiness of its customers. If it did, it wouldn’t release unintuitive garbage that requires you to learn to think like a computer to use it. They’d rather spend their time releasing insecure operating systems that are wide open to virus bombs and worm attacks that slow our network to a crawl so that those of us doing real work on non-Microsoft systems can’t even do simple tasks.
Sigh, I’m sorry. I appreciate your help. I’m just stunned, stunned, that a company that hates its users as much as Microsoft can be so successful. Expecting people to learn programming garbage to simply SORT AND FILTER A LIST is nonsense.
Is there anywhere to go where someone could just tell me what to plug into this stupid VLOOKUP thing?
All I want is EVERYTHING from every TENTH row. This should not be hard.
Thanks! I think you got me onto something. This may just be what you suggested, but what I think might work is to create a new column B, and put 9 blank lines, with a letter or number in every tenth. We can use copy and paste to do this quickly. Then sort alphanumerically using that column as the focus, which should force all of the data I’m looking for either to the top or the bottom of the list. Then I can delete the rest, and just use what I wanted to in the first place.
Is that about what you were saying? Because I got a little lost in your description.
I really appreciate all the help, from both of you guys. I’m so glad I don’t use Microsoft software on a daily basis.
Thanks for the offer, too, cm. But I won’t get the file until tomorrow morning.
What you just suggested is a little different than what I suggested (and better). You found a great way to do it.
Here is a hint for quickly filling what you called Column B:
Insert a new column (Column B)
In B1 enter: 1 (or A or Z or Anything at all)
B2 - B10 enter: nothing
Select B1 through B10 (make sure all 10 are selected!)
Double click on the bottom right corner of B10.
Column B should now be filled with every 10th row having something in it.
Do as you suggested and sort the spreadsheet based on column B and delete unwanted rows.
My offer is still good tomorrow if you run into problems.
I use excel all the time, so I never pass up the opportunity to learn a new trick. Is that a mac thing? I tried it in Windows, and got gibberish, but not the desired result.
Alternatively you could enable the Excel function that lets you pick every tenth row.
Go to Tools. If you do not have Data Analysis as an option go to Add Ins and add it.
Now you can go to Tools/Data Analysis select Sampling and output a sample of your data by selecting Periodic with a period of 10. It will write every tenth row to a new worksheet.
No, I’m using Windows. However, I just noticed that you can’t pick any cell. The cell listed in the first part of your OFFSET command must be in the same row as the cell you’re copying to.
Plug that formula into cell B1 and see what happens.
put the following formula in all cells in the new column:
=MOD(ROW(), 10)
Ensure your cellpointer is in the new column and choose Data, Filter, Autofilter
a drop-down arrow appears at the top of the new column (and perhaps other columns if the region doesn’t have breaks). Pick 1 (or any number) from the drop-down list.
You now have filtered your list and are showing every tenth row. Select what you see and paste in a new sheet.
