Help! Is there an Excel Expert in the house?

[Iwasgoodonce]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.