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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,231
Messages
5,571,028
Members
412,355
Latest member
BasicExelHelp
Top