Column to Diagonal

Nightboy

New Member
Joined
Sep 14, 2002
Messages
16
Hi, I have data in a column, say 100 cells & I want to display it in the same order but in diagonal format, running down left to right. I have no idea how to do this.
thanks
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
OK, Suppose your column of data is housed in cells A1:A10 (I've used 10 data points for my quick example).

In cell B2 put in this formula:

=IF(ROW(B1) = COLUMN(B1), $A1, "")

now copy this formula over to column J and down to row 10. You shoulw get the values in diagonal.

Now:

1. Select all of the values and do a Copy|Paste Special|Values

2. delete all of the values in column A except for the first value in A1.

That should do it.

HTH

EDIT:: Nightboy, yeah, I meant to say "B1", not "B2"
_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-09-15 19:19
This message was edited by Mark O'Brien on 2002-09-16 07:57
 

Jack in the UK.

New Member
Joined
May 22, 2002
Messages
28
Here's one way :-

Dim rng As Range, rng2 As Range, x%
Set rng = [A1:A100]
For x = 2 To rng.Rows.Count
If rng2 Is Nothing Then
Set rng2 = rng(x)
Else
Set rng2 = Union(rng(x).Resize(1, x - 1), rng2)
End If
Next
rng2.Insert Shift:=xlToRight
 

Nightboy

New Member
Joined
Sep 14, 2002
Messages
16
Thanks Mark, that works although I found I had to put the formula in B1 rather than B2. Also to Jack, I think that your answer might involve VBA (?) which I don't know how to use. I will stick with the normal Excel commands.
 

Forum statistics

Threads
1,147,737
Messages
5,742,896
Members
423,760
Latest member
photogfrog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top