help wanted to clean up a data sheet

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

0
jvoortman (40)
10/21/2005 1:38:14 PM
excel.misc 78881 articles. 5 followers. Follow

9 Replies
470 Views

Similar Articles

[PageSpeed] 54

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

0
10/21/2005 2:17:43 PM
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

"jvoortman" <jvoortman@canada.com> wrote in message 
news:1129901893.966197.294330@g47g2000cwa.googlegroups.com...
>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
> 


0
ng1 (1444)
10/21/2005 2:24:49 PM
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

0
10/21/2005 3:02:16 PM
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.

damorrison wrote:
> 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

0
jvoortman (40)
10/21/2005 4:26:52 PM
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.

0
jvoortman (40)
10/21/2005 6:15:57 PM
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" <jvoortman@canada.com> wrote in message 
news:1129918557.638458.7310@g49g2000cwa.googlegroups.com...
> 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.
> 


0
ng1 (1444)
10/21/2005 6:31:05 PM
awesome Anne Troy, where do I send the fie? my email address is
jvoortman@canada.com

0
jvoortman (40)
10/21/2005 8:55:57 PM
Thank you for all the help. By the way....HAPPY BIRTHDAY! (this past
Friday).


Anne Troy wrote:
> 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" <jvoortman@canada.com> wrote in message
> news:1129918557.638458.7310@g49g2000cwa.googlegroups.com...
> > 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.
> >

0
10/24/2005 2:14:07 PM
Glad I could help. :) Thanks!
************
Anne Troy
www.OfficeArticles.com

<jvoortman@sympatico.ca> wrote in message 
news:1130163247.092458.79030@f14g2000cwb.googlegroups.com...
> Thank you for all the help. By the way....HAPPY BIRTHDAY! (this past
> Friday).
>
>
> Anne Troy wrote:
>> 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" <jvoortman@canada.com> wrote in message
>> news:1129918557.638458.7310@g49g2000cwa.googlegroups.com...
>> > 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.
>> >
> 


0
ng1 (1444)
10/24/2005 2:38:56 PM
Reply:

Similar Artilces: