Hello!

I have some data in excel and I need to select every other row. Other than going through and deleting every other row (which will be very time consuming, 10,000 rows of data) is there a way of doing this? I'm pretty sure there should be but I'm struggling to work it out.

Any help???? You'll have my undying gratitude!

A

Well one thing you could do is create an extra column with alternate 'yes' and 'no' in them.. So for the first two rows type yes in the line you want and no in the line you dont want.. then select the two boxes.. copy them then select the whole 'yes/no' column by clicking once on the column head (this should highlight the whole column) then paste the yes and no into it.. this should then do alternate yes and no down that column.. Then select all the columns containing your data and go to the 'data' tab and click sort in the drop down menu.. this should bring up a sort box.. you can then use this to sort the yes no column so all the 'yes' rows are together. You can then get rid of all the 'no' rows.

Its been a long day so this might be hard to follow.. if it is let me know and i can try and explain it better!

======= Date Modified 13 Oct 2008 22:08:47 =======
I don't think you can do this without writing a VBA script. Here's some help:

http://www.mrexcel.com/archive/VBA/2508.html

http://support.microsoft.com/kb/213610

EDIT: Tricky's idea is excellent!

Tricky! Where were you when I needed you? I had the same problem and just deleted alternate rows zzzzzzzzzzzzzzzzzzzzzz.

Fantastic!

Thank you both so much. My head is very fuzzy now. It's been a long day and the first one since my holiday so I will give it my full attention first thing tomorrow.

Thanks thanks thanks!!!!

A

Taking the tip from Tricky (don’t mean to be treading on toes here but see next paragraph down for solution to allow you to select whichever interval you wanted e.g. every third/fifth or whatever row), ya could extend this further. But first just to go thro' the steps of a Tricky-esque solution
a. Type some content into the first cell of the first row e.g. A
b. Then the next row blank
c. Type A into the third row cell
d. Leave the next row blank again
e. Now select these four cells and by using the AutoFill handle, drag these values all the way down the table (will take only a few seconds)
f. Once the last step is complete, go to the top of your table and select Filter Data, AutoFilter from the Data menu
g. Now select Blank Cells or Non-Blank cells as your needs be and hey presto!
h. Select that sheet and copy, paste special, values to a blank sheet and you have your data.

Extending this a bit further, there is actually a safer way of doing this by having two more columns, one with the number of the row (again generated by using the Autofill handle) the second column with the Mod function i.e. if looking for every 5th value, you would set 5 as being the divisor and then filter all 0 values (the Mod function is the Modulus function which basically finds if there is a remainder when you divide a specific number by a divisor e.g. =Mod(23,5) would equal to 3 (God bless those C programming tutorials)

Sorry if that seems over elaborate but it really isn't. If there could be a way to t/f files, I could have a solution sent to ya by 10.00 tomorrow morning. If ya want to use it, feel free to contact me. Can set up a dummy email account quickly and t/f the file if you wish to remain anon.

Long story short is that it is very simple to do and I'm sure either of the proposed solutions will work fine. This one does not involve VBA code though which might be safer.

I would just input the file as a text file and write some short code to put alternative lines into two new text files.

Maybe ... but I would reckon that whatever solution A116 needs we would have to provide some means of getting the Excel file to them, rather than confuse him/her (no offense, but it would take us a few minutes to do). If there was a means of file t/f (yeah, I know the security implications) it might facilitate this.
Main thing is that the solution to the problem is easy enough and if needed, several can be provided.

I know, thats why I thought Tricky's idea might be better.

A116, do you use Linux as well as well as windows?

H - totally agree. It is to the point and easily understandable, which would give A116 results quicker. If it is the case that he/she needs to work extensively with Excel, I'd agree that VBA would have to be looked into for the future.

Sorry for the digression lads and ladies, but do you do much coding yourself H? Looking at the prospect of using OS code to implement some DSS functionality for small companies and to be honest, scared of learning a new language at this stage. Might front-end a prototype with VB. Do you know of any good forums for coding?

Hi Bonzo, I do quite a bit, using OS libraries. Have you looked at SourceForge? There might be something you can use from there.

With regards to learning a new language, if you know an OO language reasonably well, it's quite straight forward to switch to others, especially scripting languages. Which one were you considering? I think Java is probably easier than C++ but that could be my personal opinion! VB is one of the simpler ones.

To be quite honest, going to use an OS simulation tool and hopefully front end it, possibly using Java (an inkling of using Simpy as the simulation engine but only an inkling). Long road ahead. Trying to answer the requirements firstly and then start off small. Been putting it off but gotta do it now. Not an OO coder (more C/assembly) but sure how hard can it be :p

A116 - any update on the scenario?

You can get a really nice front end with Java, the swing toolkit is quite straight-forward to use. There are plenty of Java-based forums if you get stuck so definitely have a bash at it.

======= Date Modified 15 Oct 2008 10:15:00 =======
H, Bonzo, Tricky,



Thank you all so much!  Wow, you are those really scary clever kind of people...scary...but amazing!



Sorry for going off the radar yesterday. I have so much work to do and I'd love to say that I was working my way productively through it but in reality I was paralysed with fear and spent my day in my PJs under a blanked watching ER.  I'm one walking PhD cliche.



In the end I used Trickys idea because it was first and it worked for what I needed (I know, commitment isn't my strong point). Also because the words code, VBA, programming and Linux scare me.  When someone wrote C++ I nearly fainted. Flash back to hours trying to understand my ex-boyfriends love for the language!



H - I don't use Linux but my boyfriend is forever hassling me to.  He has a duel boot up system. It's silly really because I REALLY like the philosophy behind open source software and actually, my PhD is kind of related to this kind of thing.  I'm capable with computers but by no means competent. Boo!



Smilodon - Sorry you did it the long way.  At least now you know for next time, right?



Thank you all again.



A

Awww A116, at least you got it sorted.

Definitely have a shot at Linux, it really is so much better than Windows!