merging and transposing cells
 

Below is an example of the data i need modified. I do this to data several
times a month and then save it as comma delmimted for another system. If
there is anything you can do to help me in all or part, please let me know.
here is my data...

2 columns
a b
212 2132
213 21321
465 1669
7948 8989
.... ...

This goes on for 40+ lines.

i then need the data to be in one cell merged together like so...

2122132
21321321
4651669
79488989
....

once done i need the data to be transposed like so...

a b c d...
2122132 21321321 4651669 79488989...

try this url and see whether you get what you want

Public Sub test()
Dim cell As Range
Dim cellone As Range
Dim myrange As Range
Set cellone = Range("a1")
Set myrange = Range(cellone, cellone.End(xlDown))
For Each cell In myrange
cell.Activate
ActiveCell.Offset(0, 2) = ActiveCell & ActiveCell.Offset(0, 1)
Next
Range(cellone.Offset(0, 2), cellone.Offset(0, 2).End(xlDown)).Select
Selection.Copy
cellone.Offset(0, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
False, Transpose:=True
Application.CutCopyMode = False
Columns("a:c").Delete
End Sub

Daniel M wrote in message
k.net...
Below is an example of the data i need modified. I do this to data several
times a month and then save it as comma delmimted for another system. If
there is anything you can do to help me in all or part, please let me
know.
here is my data...

2 columns
a b
212 2132
213 21321
465 1669
7948 8989
... ...

This goes on for 40+ lines.

i then need the data to be in one cell merged together like so...

2122132
21321321
4651669
79488989
...

once done i need the data to be transposed like so...

a b c d...
2122132 21321321 4651669 79488989...

One option is:

1. To join the two cells in colmn C:
=A1&A2
copy down

2. To transpose: Write this function in D1:
=OFFSET($C$1,COLUMN()-4,0)
Copy this to E1...AN1?


Ola Sandstrom

Daniel M,

why not make a "helper column" "c" and concatenate column A and B in your
new column"C". "Copy" & "paste special values". When this is populated
select the "40+ lines" Copy and "Paste Special Transpose" to the 1st column
that you want to hold the new data.

HTH
Lomax

Have a great day..


"Daniel M" wrote in message
k.net...
Below is an example of the data i need modified. I do this to data several
times a month and then save it as comma delmimted for another system. If
there is anything you can do to help me in all or part, please let me
know. here is my data...

2 columns
a b
212 2132
213 21321
465 1669
7948 8989
... ...

This goes on for 40+ lines.

i then need the data to be in one cell merged together like so...

2122132
21321321
4651669
79488989
...

once done i need the data to be transposed like so...

a b c d...
2122132 21321321 4651669 79488989...