Problem when trying to convert one column with multiple rows to one row with multiple column

  • Thread starter Thread starter marcello
  • Start date Start date
M

marcello

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.
 
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


..
 
Back
Top