Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
help wanted to clean up a data sheet
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 |
#3
|
|||
|
|||
help wanted to clean up a data sheet
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/r...osoft_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/...soft_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" wrote in message oups.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 |
#4
|
|||
|
|||
help wanted to clean up a data sheet
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 |
#5
|
|||
|
|||
help wanted to clean up a data sheet
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 |
#6
|
|||
|
|||
help wanted to clean up a data sheet
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. |
#7
|
|||
|
|||
help wanted to clean up a data sheet
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" wrote in message ps.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. |
#8
|
|||
|
|||
help wanted to clean up a data sheet
|
#9
|
|||
|
|||
help wanted to clean up a data sheet
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" wrote in message ps.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. |
#10
|
|||
|
|||
help wanted to clean up a data sheet
Glad I could help. :) Thanks!
************ Anne Troy www.OfficeArticles.com wrote in message ups.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" wrote in message ps.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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
enter data on 1 sheet and make it enter on next avail row on 2nd s | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Urgent: How to bring data from another sheet into a droplist | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Append the data given in diff sheets of an Excel File to one sheet | Excel Worksheet Functions |