Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Ok! basically i'm working on an excel spreadsheet in which I have to delete every alternate row because it is messing up with my results.

Its a set of 4000 randomly generated set of data.

Hence, deleting 2000 wud take about 30 mins, can it be done in 30 seconds using EXCEL alone and avoiding Programming?

Is there any trick to do that in MS Excel?

PS: I want the deleted cell to be empty and not to be replaced with the following entry.

Thanks in advance!

share|improve this question

5 Answers 5

You can use a macro to remove alternating rows, or you can cheat.

If it's sort of a one-off thing, I would cheat and add another column titled something like even. Then setup your data so it appears as the following:

value    item    even
10       foo     1
2        bar     2
11       baz     1
13       qux     2

You'd then filter based on the rows you wish to delete, select them and delete them. Filling out 1 and 2 is generally easy to just type the first four entries by hand, select the four values (1 2 1 2) and then double click the black box on the bottom right of the selection.

My hunch is that you might not even want to be doing that. In general whenever I've run across this particular need I've found a better way to solve it using Excel's built in tools, such as pivot tables.

share|improve this answer
    
That double clicking doesn't work for me, otherwise it would be a nice solution. I use the modulo function for the same effect. –  TToni Nov 29 '10 at 19:32
    
How long does it take? –  user57299 Nov 29 '10 at 19:37
    
between 10 seconds and a minute depending on how savvy you are with excel. –  Sparr Nov 29 '10 at 22:21
    
@TToni MOD() is a good way to go. I tend to go the lazy way by either dragging to expand the selection or the double clicking just because I need it so rarely. –  Tyler Dec 1 '10 at 1:07

A simple VBA script will do just fine. Save a Macro-Enabled copy of your Excel document. Create a macro, paste this code and click run.

Sub DeleteAlternateRow()
    For i = 1 To 4000
        Cells(i, 1).Select
        ActiveCell.EntireRow.Delete
    Next i
End Sub

Note: This macro deletes row 1,3,5,... if you want to start with deleting the second row then start the for loop with 2. Don't forget to make a copy of your document before running any macros

share|improve this answer
    
my VBA is rusty, shouldn't this be "For i = 1 to 4000 step 2"? –  Sparr Nov 29 '10 at 22:22
    
Doing so would delete all the cells. Each time a row is deleted the next one automatically gets activated so the first step makes sure to skip one cell at a time. –  Gani Simsek Nov 30 '10 at 7:46

In a new column (say "X") use

=MOD(LINE(X1);2)

and expand the column over the whole set. That should give you an alternating sequence of 0 and 1. Now auto-filter and select only the 0s (or the 1s), then hit delete.

You may have to look up your local version of the functions, if you are not using english excel. For example in Germany it would be REST(ZEILE(X1;2)). MOD is the modulo function, LINE should give you the current line number.

share|improve this answer
    
I like this one. much simpler than the MS macro i found –  madmaze Nov 29 '10 at 19:50

say your data is in column A, you wish to get only data from odd number rows.

Step 1: click on a different column, say B

Step 2: in row 1, column B write "=A1"

Step 3: click on that cell so it lights up blue

Step 4: also select the cell beneath it, you should now have two cells blue B1 and B2

Step 5: use the drag function found in the bottom right corner of cell B2 and drag down

note: make sure to drag it all the way to the bottom on the first try

share|improve this answer

Copying alternate rows of data into a column

Formula option

starting in row1, for the even positions:

=INDEX(A:A,1+(2*ROW()-1))

for the odd:

=INDEX(A:A,1+(2*ROW()-2))
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.