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.

Hi,
This is close, but it adds an extra "," at the very end. Run it on each
column separately, although it will work on any column, not just A & B.
Sub Macro1()
ReturnAddress = ActiveCell.Address
RowCombine = ActiveCell.Value
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Range("A1").Select
RowCombine = RowCombine & "," & ActiveCell.Value
Loop
Range(ReturnAddress).Select
ActiveCell.Value = RowCombine

End Sub

Thanks,

"J" wrote:

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.

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.

Here is code that I use to create the csv and to then split the csv. It is a
little different from exactly what you want but it is close. It takes a row
of data and makes a csv line on a new sheet. Highlinght the row you want and
run the code.

Private Const m_cMaxConcatenateRows As Integer = 1000

Private Sub MakeCSV()
Dim rngCurrent As Range
Dim rngToPaste As Range
Dim intCounter As Integer
Dim wksPasteTo As Worksheet

intCounter = 0

Set rngCurrent = ActiveCell
Set wksPasteTo = Worksheets.Add
Set rngToPaste = wksPasteTo.Range("A1")

rngToPaste.NumberFormat = "@"
rngToPaste.Value = rngCurrent.Value
Set rngCurrent = rngCurrent.Offset(1, 0)

Do While rngCurrent.Value < ""
intCounter = intCounter + 1
If intCounter m_cMaxConcatenateRows Then
intCounter = 0
Set rngToPaste = rngToPaste.Offset(1, 0)
rngToPaste.Value = rngCurrent.Value
Set rngCurrent = rngCurrent.Offset(1, 0)
Else
rngToPaste = rngToPaste & ", " & rngCurrent
Set rngCurrent = rngCurrent.Offset(1, 0)
End If
Loop
End Sub

Private Sub SplitCSV()
Dim aryCSVValues As Variant
Dim rngPasteTo As Range
Dim intCounter As Integer
Dim wksPasteTo As Worksheet

aryCSVValues = Split(ActiveCell.Value, ",")
Set wksPasteTo = Worksheets.Add
Set rngPasteTo = wksPasteTo.Range("A1")

For intCounter = LBound(aryCSVValues) To UBound(aryCSVValues)
aryCSVValues(intCounter) = Trim(aryCSVValues(intCounter))
If Left(aryCSVValues(intCounter), 1) = "'" Then _
aryCSVValues(intCounter) = Right(aryCSVValues(intCounter),
Len(aryCSVValues(intCounter)) - 1)
If Right(aryCSVValues(intCounter), 1) = "'" Then _
aryCSVValues(intCounter) = Left(aryCSVValues(intCounter),
Len(aryCSVValues(intCounter)) - 1)
rngPasteTo.NumberFormat = "@"
rngPasteTo.Value = aryCSVValues(intCounter)
Set rngPasteTo = rngPasteTo.Offset(1, 0)
Next intCounter
End Sub

HTH

"J" wrote:

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.

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.

Perfect, this does exactly what I asked for. But I didn't ask for exactly
what I needed.
I am using this to modify some data in SQL and I just realized that I need
the individual values to be encapsulated in single quotes. Any ideas on that?
Thanks so much for the help.

"David" wrote:

Hi,
This is close, but it adds an extra "," at the very end. Run it on each
column separately, although it will work on any column, not just A & B.
Sub Macro1()
ReturnAddress = ActiveCell.Address
RowCombine = ActiveCell.Value
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Range("A1").Select
RowCombine = RowCombine & "," & ActiveCell.Value
Loop
Range(ReturnAddress).Select
ActiveCell.Value = RowCombine

End Sub

Thanks,

"J" wrote:

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.