I have several worksheets where I need to take rows of data like:
Row1 Row2
ABCDEF 12
GHIJKL 34
MNOPQR 56
And I want to transpose that into 2 comma-delimited rows like:
Row1 ABCDEF,GHIJKL,MNOPQR
Row2 12,34,56
The paste special function separates the data into columns. I probably need
to write a macro but I don't have much experience doing that. I appreciate
the help in getting this done. Thanks.

try this:

1. Copy the range you want to transpose
2. Select a cell on another sheet
3. Click on Paste Special under the edit menu
4. Click on the Transpose checkbox
5. Click on OK

You will have the values you copied transposed.

Hope this helps and if it does, please vote for this post.

G.Morales

"J" wrote:

I have several worksheets where I need to take rows of data like:
Row1 Row2
ABCDEF 12
GHIJKL 34
MNOPQR 56
And I want to transpose that into 2 comma-delimited rows like:
Row1 ABCDEF,GHIJKL,MNOPQR
Row2 12,34,56
The paste special function separates the data into columns. I probably need
to write a macro but I don't have much experience doing that. I appreciate
the help in getting this done. Thanks.

If your data is like this
A1="ABCDEF",B1="GHIJKL",C1="MNOPQR" (row 1), and
A2=12,B2=34,C2=56
then use this formula in D1
=A1 & "," & B1 & "," & C1
and copy the formula down to D2

If you want to automate the above process for a number of open workbooks then
create a new module and place the following code into it
Sub CreateCommaDelimitedFormula()
Dim wb As Workbook
For Each wb In Workbooks
'the following line assumes the formula is required in Sheet1 of
each
'workbook and only for Cells D1 and D2
With wb.Worksheets("Sheet1")
With .Range(.Cells(1, 4), .Cells(2, 4))
.FormulaR1C1 = "=RC[-3] & "","" & RC[-2] & "","" & RC[-1]"
End With
End With
Next wb
End Sub


Alok Joshi


"J" wrote:

I have several worksheets where I need to take rows of data like:
Row1 Row2
ABCDEF 12
GHIJKL 34
MNOPQR 56
And I want to transpose that into 2 comma-delimited rows like:
Row1 ABCDEF,GHIJKL,MNOPQR
Row2 12,34,56
The paste special function separates the data into columns. I probably need
to write a macro but I don't have much experience doing that. I appreciate
the help in getting this done. Thanks.

Sorry, that's columns of data

"J" wrote:

I have several worksheets where I need to take rows of data like:
Row1 Row2
ABCDEF 12
GHIJKL 34
MNOPQR 56
And I want to transpose that into 2 comma-delimited rows like:
Row1 ABCDEF,GHIJKL,MNOPQR
Row2 12,34,56
The paste special function separates the data into columns. I probably need
to write a macro but I don't have much experience doing that. I appreciate
the help in getting this done. Thanks.