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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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
Back
Top