I have an excel document with about 16,000 lines of data. The problem is that the data is on every other line. Is there a fast way to get rid of all the blank lines?

 

sort it?

 

Might work

but other than that
I know of no quick way in excel to do that.

I would convert it to csv file and then use grep -v to get rid of the blank lines.

 

can u please be a bit more specific on this method

 

Just sort it

 

Well

You need to have unix tools on your machine, or there might be a freeware grep utility out there.


convert to CSV as long as there isn't special formatting in the file you will be ok

grep , filename.csc > newfilename.csv

re-import back into excel.

 

can u give exact directions, cause I have 4 columns of info and it can't be mixed up

 

If you want to keep the rows in the order they are in now (without the blank rows)

Add a new column A
Number it sequentially (1 - n)
Sort on column B
Now the the blank rows will be at the bottom (depending if you choose ascending or descending - doesn't really matter either way)
Delete blank rows
Resort by column A
Delete column A
Done

 

 

Thanks.

I spend probably 6 hours a day manipulating Excel spreadsheets.

 

Sounds like another classic case of Excel being used as a database.

 

AMAZING

this method worked, thank you very much

but now I have another dilema...

I'm in MS Word, I imported that excel doc into mail merge, everything came out fine, and I have 291 pages of labels. Now my dilema is that I gotta insert "Queens, NY" into each label right before the zip code. Any fast way of doing that?

 

Insert a new column?
You'll have to rerun the mail merge to grab the new column.

 

find and replace zip code with queens ny + zip code?

 

problem is that the zip code is on the same line with the address, cause in excel the zip was in the same column with the address

 

I think it should still work if you don't include the rest of the address. It should leave the rest of the field alone. Try it on one field and see if it works.

 

sort?

 

Redo the mail merge so the address is on one line and Queens and the zip is on the other.

 

what I did do far was go back to he excel doc and added a new column with Queens ,NY.

now when I crest a new mail merge, I insert all the fields, but when I try to insert the queens ny field between the address and zip, I hit the button to update all labels, and it doesnt include the queens ny field

what now??

 

Use Access.

 

right now thats not an option

 

Are you familiar with TEXTPAD (freeware, though if you look you can register it...)?

Using textpad you could do it 1 of 2 ways:

1) Block copy.
2) Find and Replace using Regular Expressions.

 

fixed.

Don't use a screwdriver to hammer a nail.

 

Split the current address into two cells:
That should give you the stree address in column B and the zip code in column C

 

this is exactly what I was looking for at the time, but me and a few other co workers figured out an alternate solution that worked out very well

thanks alot for all your help guys, you're the best

 

You'll get the bill in the mail.

 

wow, AZ FTW again. great job guys...

 

Do people actually use access? I thought it wasn't that popular of a database program.

 

I use it all the time... and I can get things done much faster than those who are bound by Excel. I can also do analytics with Access that are simply impossible with Excel.

It handles much larger data sets than Excel, is consistent with types, and can of course do the relational database things that Excel can't.

 

Well I know it's better than excel but I mean compared to other db programs, is access the most popular? If so is that cause it's actually the best or because it comes with some versions of Office?

 

The interoperation with the other Office applications is a huge benefit for the type of work I do with it.

I don't know of another desktop relational database that is as easy to use, much less one that offers the integration with Office that Access has.

Note that I use it on my desktop for the relational engine, and never implement a GUI front end or any kind of multiuser access / security.

 

Just an FYI here but there's another way to get rid of the blank rows that doesn't require any new columns or formulas.

1. Highlight the affected area only
2. Click Edit > Go To
3. Click "Special" button
4. Tick "Blanks" and hit OK
5. You should see all the blank row highlighted now.
6. Edit > Delete > Shift cells up
7. Done. Blanks gone! Used this many times.