|
Text to Row automation
Hello all,
I have no experience in Excel VBA (except for modifying a few recorded macros), but am faced with a very complicated data clean-up project which will take forever unless I can automate it. My original data looks like this: DocumentNumber Companies Date Type Description 12 CoA, CoB 1/1/07 Type1 Doc12description 15 CoA 12/15/06 Type2 Doc15description 26 CoB, CoC,CoD,CoE 12/12/06 Type1 Doc26description I need my final spreadsheet to look like this: DocumentNumber Companies Date Type Description 12 CoA 1/1/07 Type1 Doc12description 12 CoB 1/1/07 Type1 Doc12description 15 CoA 12/15/06 Type2 Doc15description 26 CoB 12/12/06 Type1 Doc26description 26 CoC 12/12/06 Type1 Doc26description 26 CoD 12/12/06 Type1 Doc26description 26 CoE 12/12/06 Type1 Doc26description I was thinking that if I added a column indicating the number of items in the column B for each row, I could somehow set a variable to that number, add the appropriate number of rows, copy the data that's the same on each row, and then somehow parse the data from column B into each of the separate rows. But I have no idea how to go about this in Excel ... my only experience with VBA (and that's not much) is in Access. I have written some macros to automate bits of this, but I still have to step through each row and count the item numbers myself. It's better than doing it by hand, but I'm thinking there must be a way I can run the whole worksheet at one time. Any pointers? |
Text to Row automation
Hi, you should remember one thing while programming for any office component,
which is, all are objects and expose methods and properties. If you are good in programming for access, all you have to do is, use the relevant objects for excel. In your case, the solution would be a simple do-while loop to read a text file and store the contents into either an excel worksheet or a table in access database. "DianeM" wrote: Hello all, I have no experience in Excel VBA (except for modifying a few recorded macros), but am faced with a very complicated data clean-up project which will take forever unless I can automate it. My original data looks like this: DocumentNumber Companies Date Type Description 12 CoA, CoB 1/1/07 Type1 Doc12description 15 CoA 12/15/06 Type2 Doc15description 26 CoB, CoC,CoD,CoE 12/12/06 Type1 Doc26description I need my final spreadsheet to look like this: DocumentNumber Companies Date Type Description 12 CoA 1/1/07 Type1 Doc12description 12 CoB 1/1/07 Type1 Doc12description 15 CoA 12/15/06 Type2 Doc15description 26 CoB 12/12/06 Type1 Doc26description 26 CoC 12/12/06 Type1 Doc26description 26 CoD 12/12/06 Type1 Doc26description 26 CoE 12/12/06 Type1 Doc26description I was thinking that if I added a column indicating the number of items in the column B for each row, I could somehow set a variable to that number, add the appropriate number of rows, copy the data that's the same on each row, and then somehow parse the data from column B into each of the separate rows. But I have no idea how to go about this in Excel ... my only experience with VBA (and that's not much) is in Access. I have written some macros to automate bits of this, but I still have to step through each row and count the item numbers myself. It's better than doing it by hand, but I'm thinking there must be a way I can run the whole worksheet at one time. Any pointers? |
Text to Row automation
|
Text to Row automation
You can easily add a row with
Range(Cells(RowCount, 1), Cells(RowCount, SourceLastCol)).Insert (xlShiftDown) You can use string commands to find the position of wher the first commar is located. position = instr(old_string, ",") if position 0 short_string = left(old_string, position - 1) 'then extract evverything left of the commmar old_string = Mid(old_sttring, position + 1) end if "DianeM" wrote: Hello all, I have no experience in Excel VBA (except for modifying a few recorded macros), but am faced with a very complicated data clean-up project which will take forever unless I can automate it. My original data looks like this: DocumentNumber Companies Date Type Description 12 CoA, CoB 1/1/07 Type1 Doc12description 15 CoA 12/15/06 Type2 Doc15description 26 CoB, CoC,CoD,CoE 12/12/06 Type1 Doc26description I need my final spreadsheet to look like this: DocumentNumber Companies Date Type Description 12 CoA 1/1/07 Type1 Doc12description 12 CoB 1/1/07 Type1 Doc12description 15 CoA 12/15/06 Type2 Doc15description 26 CoB 12/12/06 Type1 Doc26description 26 CoC 12/12/06 Type1 Doc26description 26 CoD 12/12/06 Type1 Doc26description 26 CoE 12/12/06 Type1 Doc26description I was thinking that if I added a column indicating the number of items in the column B for each row, I could somehow set a variable to that number, add the appropriate number of rows, copy the data that's the same on each row, and then somehow parse the data from column B into each of the separate rows. But I have no idea how to go about this in Excel ... my only experience with VBA (and that's not much) is in Access. I have written some macros to automate bits of this, but I still have to step through each row and count the item numbers myself. It's better than doing it by hand, but I'm thinking there must be a way I can run the whole worksheet at one time. Any pointers? |
| All times are GMT +1. The time now is 12:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com