Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem when trying to convert one column with multiple rows to one row with multiple column
I have 100 files with 200 records(approx)each one that had been copied
in one column.They are tab or comma delimited. Each record has different number of rows. They are separed by a line (-----)first and then the second row has a number. I have a solution that is copy one by one and using paste special and traspose convert them from one column and multiple rows in one row with multiple columns. The problem is that I have 12000 records and it can take me my whole life to do it in this way. I tried to use the command columns to rows but it does not work.(because each record has different number of rows) Is there a macro or other feature of excel that allow me to 1.- convert the record from one column multiple rows to one row multiple column .? 2.- copy all the records in one file? I will really appreciate if someone can help with this problem. Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem when trying to convert one column with multiple rows to one row with multiple column
Marcello,
This is going to take a bit of work (still), but we can speed up the process. First let's define the problem a little. you need to convert vertical information with variable numbers of rows, with variable numbers of records to horizontal data. The trigger for a new record is "-----" so we will use that to determine whether or not the current row is within the previous record. I assume that there are no blank rows in the original data. we will then need to copy the data to a resulting sheet and repeat the process until we get blank rows which will indicate the end of the last record. So, we have a workbook containing source info, and we will need a workbook to copy the data into (results.xls). The following code is untested and should be run from the source workbook Private Sub copy_data() dim source as string dim delimiter as string delimiter = "-----" source = activeworkbook.name do until activecell.value="" Range("A1").select 'assumes A1 is "-----" selection.entirerow.delete range("A1").select do until activecell.offset(1,0).value = delimiter activecell.offset(1,0).select loop range("A1:" & activecell.address).select selection.copy workbooks("Results.xls").activate selection.pastespecial transpose:=true activecell.offset(1,0).select workbooks(source).activate selection.entirerow.delete loop end sub so we remove the first record delimiter loop through until we find that the next row is a delimiter copy from a1 to wherever we are paste transpose into Results.xls, and move down one row switch back to the source book delete the highlighted rows go back to cell a1 and delete the record delimiter which shold now be there and go through the process again until we reach an empty cell. As I said, this is untested, and may have a bug or three, but using the step through command, you should be able to find and correct the bugs (if any) Steve .. -----Original Message----- I have 100 files with 200 records(approx)each one that had been copied in one column.They are tab or comma delimited. Each record has different number of rows. They are separed by a line (-----)first and then the second row has a number. I have a solution that is copy one by one and using paste special and traspose convert them from one column and multiple rows in one row with multiple columns. The problem is that I have 12000 records and it can take me my whole life to do it in this way. I tried to use the command columns to rows but it does not work.(because each record has different number of rows) Is there a macro or other feature of excel that allow me to 1.- convert the record from one column multiple rows to one row multiple column .? 2.- copy all the records in one file? I will really appreciate if someone can help with this problem. Thank you. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert multiple columns into on column? | Excel Discussion (Misc queries) | |||
Convert data from multiple columns/rows into one column - help! | Excel Worksheet Functions | |||
Convert Multiple Column Values into 1 column | Excel Worksheet Functions | |||
How can I convert a data from multiple rows into 1 column? | Excel Worksheet Functions | |||
how do I easily convert a single column of text (multiple rows si. | Excel Discussion (Misc queries) |