Help! Is there an Excel Expert in the house?

Iwasgoodonce · Jan 20, 2008

Very off topic, but this forum is the font of all knowledge! I have over 5000 lines of Excel rows to edit. Basically I have to delete every other line in the file to remove erronious data from a survey file I'm working on. There must be an easier (well quicker, anyway!!) method other than holding the 'ctrl' key down and selecting alternate rows? I also have TextPad if anyone is an expert on that.

HELP! Or it's going to be a very long and dull day.

Dave_H · Jan 20, 2008

There's probably easier ways but you could stick in another column and put in a 0 (zero) in the top cell. Then do a simple formula in the next cell down that's if(A1=0,1,0) , where A1 is the cell with the 0. Copy this down the remainder of the column and you should get alternating 0 and 1. You can then sort all on this column and delete all the rows with a 0.

TheGreenRabbit · Jan 20, 2008

Thinking about this the easyest way is very much like Dave_H's way.

1 - insert another coloum

2 - put a figure in the coloumes you want (eg 1)

3 - sort

4 - delete the ones you dont want

and Robert is a relative of a primate.

amt27 · Jan 20, 2008

if you need to keep the blank rows you will need to do this,

1. insert a new collumn A with numbered 1,2,3,...,4999,5000
2. insert a new collumn B, with 0 then 1 then 0 then 1 0 1 0 1 0......... do first 3 and drag
3. sort B so you have all 00000s and then 11111s, delete the cells you dont wants, remember to keep collumn A cells,
or, sort B so you have all 00000s and the 11111s, copy collumn A to another sheet, clear contents of all the rows you dont want, copy/replace back collumn A from the other sheet to collumn A,
4. sort collumn A 1-5000 and delete it,

REKIBorter · Jan 20, 2008

Filter the column, chose BLANKS - this will display all the empty rows and then highlight the rows and delete them. Unfilter the column and Roberts you mothers brother.

Iwasgoodonce · Jan 20, 2008

Hey, you chaps are first class! Thanks everyone. I reckon you have just saved me around five hours! Once again peace (relative of course) reigns in the house. Daddy CAN take the male half of the snot twins to football training because he no longer has to edit nearly 3000 lines of data out!!

Thanks again!!

Kestonian · Jan 20, 2008

Oh I love Excel (funny that is it funds my livelihood)

So many ways to skin a cat. Sorry I didn't see this earlier, but top marks to solutions given.

For the record, I'd have done a formula of =Mod(Row(),2)=0 and copied down for your range to give alternate True / False, sorted and deleted

Excel solutions always available for hire in exchange for parts or cash!

Woody

REKIBorter · Jan 21, 2008

Kestonian":3n1zqklr said:
Excel solutions always available for hire in exchange for parts or cash!

Woody

OK, how do I convince my boss that I don't have a 'ONE' button when he needs a report generating. You know the sort ... you just have to press 'ONE' button and it's done for you!

Kestonian · Jan 21, 2008

Aha, well, that's kind of what I do - write things that do that for people.

Seriously, happy to help out if you're trying to figure something out for yourself and need some guidance. Likewise, if you want to hand the problem on to someone, I'm right here!

psyclone1976 · Jan 23, 2008

Hi, in future download and install this

http://www.asap-utilities.com/

it's got lot of create functions for deleting rows columns etc. more than you could ever need.

Help! Is there an Excel Expert in the house?

Iwasgoodonce

Old School Grand Master

Dave_H

Senior Retro Guru

TheGreenRabbit

amt27

Retro Guru

REKIBorter

Iwasgoodonce

Old School Grand Master

Kestonian

REKIBorter

Kestonian

psyclone1976

Senior Retro Guru

Similar threads

Latest posts