Retrobike Forum Index

It is currently Sat Dec 10, 2016 4:01 pm

* Login   * Register * Search  * FAQ



Post new topic Reply to topic  [ 10 posts ] 
Author Message
PostPosted: Sun Jan 20, 2008 12:37 pm 
Old School Grand Master
User avatar

Joined: Thu Jul 19, 2007 7:18 pm
Posts: 3798
Location: Staffordshire
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.


Top
 Profile  
 
 Post subject:
PostPosted: Sun Jan 20, 2008 1:05 pm 
Retro Guru

Joined: Mon Aug 21, 2006 12:10 pm
Posts: 2757
Location: High Peak
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.


Top
 Profile  
 
 Post subject:
PostPosted: Sun Jan 20, 2008 1:30 pm 
South East AEC
South East AEC
User avatar

Joined: Wed Oct 03, 2007 3:39 pm
Posts: 3882
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.


Top
 Profile  
 
 Post subject:
PostPosted: Sun Jan 20, 2008 2:35 pm 
Retro Guru
User avatar

Joined: Sun Apr 29, 2007 2:29 pm
Posts: 389
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,


Top
 Profile  
 
 Post subject:
PostPosted: Sun Jan 20, 2008 3:08 pm 
Anglian Deputy AEC
Anglian Deputy AEC
User avatar

Joined: Mon Oct 09, 2006 6:13 pm
Posts: 6928
Location: Livin' in a dust bowl
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.


Top
 Profile  
 
 Post subject:
PostPosted: Sun Jan 20, 2008 3:16 pm 
Old School Grand Master
User avatar

Joined: Thu Jul 19, 2007 7:18 pm
Posts: 3798
Location: Staffordshire
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!!


Top
 Profile  
 
 Post subject:
PostPosted: Sun Jan 20, 2008 11:14 pm 
retrobike rider / Gold Trader
retrobike rider / Gold Trader
User avatar

Joined: Thu Dec 08, 2005 12:29 pm
Posts: 2378
Location: London
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


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jan 21, 2008 8:38 pm 
Anglian Deputy AEC
Anglian Deputy AEC
User avatar

Joined: Mon Oct 09, 2006 6:13 pm
Posts: 6928
Location: Livin' in a dust bowl
Kestonian wrote:
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!


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jan 21, 2008 9:05 pm 
retrobike rider / Gold Trader
retrobike rider / Gold Trader
User avatar

Joined: Thu Dec 08, 2005 12:29 pm
Posts: 2378
Location: London
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!


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jan 23, 2008 7:58 pm 
Retro Guru
User avatar

Joined: Fri Jan 18, 2008 12:30 pm
Posts: 730
Location: Balham, London
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.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 10 posts ] 

All times are UTC [ DST ]


Who is online

Users browsing this forum: al-onestare, BerthaPog, kingbling and 17 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  

About Us

Follow Retrobike

Other cool stuff

All content © 2005-2015 Retrobike unless otherwise stated.
Cookies Policy.
bikedeals - the best bike deals in one place
FatCOGS - Fat Chance Owner's Group

Powered by phpBB® Forum Software © phpBB Group