help wanted to clean up a data sheet

[jvoortman]

I have done a cut & paste of a list from the web/library page. It has a
lot of stuff that I don't need. I could go through it manually and
delete everything I don't need, but that may take days. I am wondering
if there is a way to do it quickly and easily using some sort of code
or filters or whatever. In the end all I really need is the company
info one cell under another and then a single space and then repeat
using the next companies info and so on. I posted the origial file
along with a small sampling (done manually) of what I would like to
achieve at the URL below. Thanks in advance for any help offered.
http://excelexample.homestead.com/index.html

 

[damorrison]

Sub ForNextTest ()
'this will be your starting point
Range("yourRangeHere").select
'this is how many times you want the loop,change the 60 to what ever
you need
For varCounter = 1 to 60
'enter code here
Next
End Sub
test it out, maybe set the counter to 5or 10 to make sure its going to
work properly
dave

 

[Anne Troy]

If this were a task for me and I have no code resources, I would probably
open the file directly from Word, record myself a macro (
http://www.officearticles.com/word/record_a_macro_in_microsoft_word.htm )
that:
--Ctrl+F and find SIC
--hit the EXT in Word's status bar
--Ctrl+F and find Name
--Use the back arrow 5 times to unselect the word Name
--hit delete
--stop recording
Run that macro from a toolbar button once for each record. Do something
similar for any extra lines that you didn't want. Then, I'd copy the table
over to Excel and do something like this (See Vertical to horizontal):
http://www.officearticles.com/excel/data_cleanup_tips_for_microsoft_excel.htm
(because I would NEVER set up my data the way your 2nd image shows. Use this
method so you can use your data for mail merges and you can keep your data
in columns and rows and lay it out any way you like using mail merge.
************
Anne Troy
www.OfficeArticles.com

 

[damorrison]

I am not sure if all your details are evenly spaced but if they are you
could record a macro, in relative mode.
select your cell where you want the macro to start,
go to record maro,
select a name,
a square box will show up, click on the box and it should appear
depressed, you are now in relative mode.delete the areas you want and
select the cell in the next starting position.
stop macro
test it out a couple of times to see if it does what you want.
I will get you a loop in the next few minutes

It looks like you can delete some columns before you record this macro

 

[jvoortman]

not smart enough to understand ... I know how to click Alt F11 and
insert code in a new module, and then once back on excel sheet, do an
Alt F8 to run it. That's the extent of my knowledge....hence the
request for help.

 

[jvoortman]

they are not all evenly space unfortunately. After the company info
they list what categories they are listed under, and while some
companies fall only under one or two categories, others fall under 5 or
6. Also I just noticed that the company name is actually in a cell that
is a result of two merged cell. I guess they are really trying to stop
people from extracting a free list, or at least easy.

 

[Anne Troy]

jvoortman: I am willing to take a look at your list to see if I can quickly
convert it for you.
************
Anne Troy
www.OfficeArticles.com

 

[jvoortman]

awesome Anne Troy, where do I send the fie? my email address is
(e-mail address removed)

 

[jvoortman]

Thank you for all the help. By the way....HAPPY BIRTHDAY! (this past
Friday).

 

[Anne Troy]

Glad I could help. Thanks!
************
Anne Troy
www.OfficeArticles.com