Changing data orientation from one sheet to another

[Olga]

Hi,
On sheet1 the data is oriented vertically as
A1
A2
A3
I'd like on sheet2 the data be oriented horizontally
A1 to A1
A2 to B1
A3 to B3
Can it be done? if so, how?
TIA
Olga
Excell 07

 

[Gord Dibben]

Copy the data from sheet1

Select A1 of sheet2 and paste special>transpose.

Note: cannot be done if you have more than 16384 rows of data in column A

Also, I think you have a typo at A3 to B3.........maybe A3 to C1?


Gord Dibben MS Excel MVP

 

[Olga]

Thank you Gord,
The data is coming from a SQL query and it is text and money values. If I
copy A1 from sheet 1 and paste it as transpose on sheet2 works however, if I
drag A1 horizontally to fulfill the rest of the cells then, the information
is not coherent. Surly I'm doing some wrong.

 

[Gord Dibben]

"Surley I'm doing some wrong"

Yes, you are<g>

Re-read the instructions................I did not say to copy A1 from sheet1

Select the data range on sheet1 and copy.

Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc

No dragging needed. You are paste/transposing the copied range, not just one
cell.


Gord

 

[Olga]

Thank you very much, it worked. But how would sheet 2 update itself when
sheet 1 is updated refreshing the db connection?

 

[Gord Dibben]

Sheet2 will not update itself using the manual transpose method we just did.

We will have to link the cells to the source range then tranpose those links.

Or write a macro to do the job after each query.

What is your original source range that will be transposed?

Is it consistent or variable range?


Gord

 

[Olga]

The original sourse range is composed of two columns containing labels(A1)
and numbers (euros B1).
It's variable, every month a new row is added by the query.

 

[Gord Dibben]

If once a month, automation should not really be required.

But, this macro can be run to copy the sheet1 data to a new sheet.

Sub select_transpose()
Range(Range("A1:B1"), Cells(Rows.Count, Selection.Column).End(xlUp)).Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("A1").Select
End Sub


Gord

 

[Olga]

Hi Gord,
apparently I need to do my transformation manually or by recording a macro
because otherwise, my cells options (borders, alignments, colors, etc) get
mist up when actualizing the data/ query. Using the below test micro, only
the data is updated leaving the cells format intact. My problems is that I've
over 100 cells (a + b = 200) to manually transform. So, perhaps, I need to
think of a loop.
------------------------------------------------------
Sub Macro2()
Sheets("TEST").Select
Range("B16").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
Range("C16").Select
ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
Range("D16").Select
ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
Range("E16").Select
ActiveCell.FormulaR1C1 = "=DB!R[-11]C[-4]"
Range("B17").Select
ActiveCell.FormulaR1C1 = "=DB!R[-15]C"
Range("C17").Select
ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
Range("D17").Select
ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
Range("E17").Select
ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
Range("E18").Select
End Sub

--------------------------------------------------------------

 

[Gord Dibben]

Did you try the macro I supplied?

What alterations do you think it needs?

Why do you think you need to link the cells if the changes are to be made once a
month?


Gord

Hi Gord,
apparently I need to do my transformation manually or by recording a macro
because otherwise, my cells options (borders, alignments, colors, etc) get
mist up when actualizing the data/ query. Using the below test micro, only
the data is updated leaving the cells format intact. My problems is that I've
over 100 cells (a + b = 200) to manually transform. So, perhaps, I need to
think of a loop.
------------------------------------------------------
Sub Macro2()
Sheets("TEST").Select
Range("B16").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
Range("C16").Select
ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
Range("D16").Select
ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
Range("E16").Select
ActiveCell.FormulaR1C1 = "=DB!R[-11]C[-4]"
Range("B17").Select
ActiveCell.FormulaR1C1 = "=DB!R[-15]C"
Range("C17").Select
ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
Range("D17").Select
ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
Range("E17").Select
ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
Range("E18").Select
End Sub

--------------------------------------------------------------

If once a month, automation should not really be required.

But, this macro can be run to copy the sheet1 data to a new sheet.

Sub select_transpose()
Range(Range("A1:B1"), Cells(Rows.Count,
Selection.Column).End(xlUp)).Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("A1").Select
End Sub


Gord

 

[Olga]

Hello Gord, thank you for your support.

Yes I did test your macro; in fact, thanks to you, I learned how to use
them. I made a little change to your working macro so that it always points
to the same datasheet (DB) and paste the copy in the same worksheet (TEST).

---------------------------------------

Sub select_transpose()
Sheets("DB").Select
Range(Range("A2:B70"), Cells(Rows.Count,
Selection.Column).End(xlUp)).Copy
'Sheets.Add
Sheets("TEST").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
'Range("A2").Select
End Sub

------------------------------------

Ones pasted the information; I need to format the cells giving color, type
of text, size. in few words, making it looking nice. Now, if I run the macro
again, I lose all the cells formats, thus my work. Even if I need to do it
once a month, why repeat all the work done? That's why I thought of my
recorded macro which does leave the cells format intact updating only the
containing data. I tried to format the datasheet so that the format can be
pasted as well but it's not the same as formatting the worksheet itself, in
my case. I hope to have explained myself well, English is not my native
language and sorry for the time delay, I'm in Spain.

Olga

Did you try the macro I supplied?

What alterations do you think it needs?

Why do you think you need to link the cells if the changes are to be made
once a
month?


Gord

Hi Gord,
apparently I need to do my transformation manually or by recording a
macro
because otherwise, my cells options (borders, alignments, colors, etc) get
mist up when actualizing the data/ query. Using the below test micro, only
the data is updated leaving the cells format intact. My problems is that
I've
over 100 cells (a + b = 200) to manually transform. So, perhaps, I need to
think of a loop.
------------------------------------------------------
Sub Macro2()
Sheets("TEST").Select
Range("B16").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
Range("C16").Select
ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
Range("D16").Select
ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
Range("E16").Select
ActiveCell.FormulaR1C1 = "=DB!R[-11]C[-4]"
Range("B17").Select
ActiveCell.FormulaR1C1 = "=DB!R[-15]C"
Range("C17").Select
ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
Range("D17").Select
ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
Range("E17").Select
ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
Range("E18").Select
End Sub

--------------------------------------------------------------

If once a month, automation should not really be required.

But, this macro can be run to copy the sheet1 data to a new sheet.

Sub select_transpose()
Range(Range("A1:B1"), Cells(Rows.Count,
Selection.Column).End(xlUp)).Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("A1").Select
End Sub


Gord


The original sourse range is composed of two columns containing
labels(A1)
and numbers (euros B1).
It's variable, every month a new row is added by the query.

"Gord Dibben" <gorddibbATshawDOTca> escribió en el mensaje de noticias
Sheet2 will not update itself using the manual transpose method we
just
did.

We will have to link the cells to the source range then tranpose those
links.

Or write a macro to do the job after each query.

What is your original source range that will be transposed?

Is it consistent or variable range?


Gord

Thank you very much, it worked. But how would sheet 2 update itself
when
sheet 1 is updated refreshing the db connection?

"Gord Dibben" <gorddibbATshawDOTca> escribió en el mensaje de noticias
"Surley I'm doing some wrong"

Yes, you are<g>

Re-read the instructions................I did not say to copy A1
from
sheet1

Select the data range on sheet1 and copy.

Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc

No dragging needed. You are paste/transposing the copied range, not
just
one
cell.


Gord

Thank you Gord,
The data is coming from a SQL query and it is text and money values.
If
I
copy A1 from sheet 1 and paste it as transpose on sheet2 works
however,
if
I
drag A1 horizontally to fulfill the rest of the cells then, the
information
is not coherent. Surly I'm doing some wrong.


"Gord Dibben" <gorddibbATshawDOTca> escribió en el mensaje de
noticias
Copy the data from sheet1

Select A1 of sheet2 and paste special>transpose.

Note: cannot be done if you have more than 16384 rows of data in
column
A

Also, I think you have a typo at A3 to B3.........maybe A3 to C1?


Gord Dibben MS Excel MVP

Hi,
On sheet1 the data is oriented vertically as
A1
A2
A3
I'd like on sheet2 the data be oriented horizontally
A1 to A1
A2 to B1
A3 to B3
Can it be done? if so, how?
TIA
Olga
Excell 07

 

[Gord Dibben]

Easiest in my opinion would be to record a macro whilst doing the formatting
once then run that in conjunction with the select_transpose macro?

Once a month run the select_transpose then run the formatting macro?

Alternative method is to link the transposed data to the original columns on DB
sheet.

This macro copies from DB sheet to TEST sheet and links the cells.

Sub select_and_Link()
Sheets("DB").Select
Range(Range("A2:B2"), Cells(Rows.Count, _
Selection.Column).End(xlUp)).Copy
Sheets("TEST").Select
With ActiveSheet
.Range("A4").Select
.Paste Link:=True
End With
Application.CutCopyMode = False
End Sub

Now, one time run this John Walkenbach macro to Transpose the linked formulas
but note that I guess it would have to be run again when your query adds a row
to DB which will kill the formatting again.

Sub Transpose_Formulas()
Dim SRange As Range, dCell As Range
Dim sCell As Range, i As Integer, J As Integer
Dim str As String

'get input ranges. default box is filled by use of text
'variable set to the selected address
str = Selection.Address(False, False)
Application.ScreenUpdating = True
On Error Resume Next
Set SRange = Application.InputBox(prompt:= _
"Select the range of cells to be transposed." & Chr(10) & Chr(10) _
& "If cells do not have Formulas, Sub will end!.", _
Type:=8, Default:=str)
If Not SRange.HasFormula Then
MsgBox "Cells do not contain formulas"
End
Else
If SRange.HasFormula Then
Set dCell = Application.InputBox(prompt:= _
"Select the top left cell of the output location.", _
Type:=8)
If dCell Is Nothing Then End
On Error GoTo 0
'set single cell references for use in the next step
Set sCell = SRange.Cells(1, 1)
Set dCell = dCell.Cells(1, 1)

'loop through all cells, working backward to the top left cell
For i = SRange.Rows.Count - 1 To 0 Step -1
For J = SRange.Columns.Count - 1 To 0 Step -1
If i > 0 Or J > 0 Then
'do this for all but the first cell
sCell.Offset(i, J).Cut _
Destination:=dCell.Offset(J, i)

Else
'do top corner last. Otherwise references are changed
sCell.Cut Destination:=dCell
End If
Next J
Next i
End If
End If

End Sub


Gord

 

[Gord Dibben]

There has to be a better way<g>

I'll keep working on it.


Gord

 

[Olga]

hahaha There's a better way to do it. That is to use PIVOT in my SQL query
but that's an other story..
Thank you.