"Shift" a Triangle within a range

[mhenk]

I work a fair amount with triangular data, and I'm wondering if someone can help me with an array formula (or VBA approach) to perform the following "shifting" of the values of the cells. Starting with an m x n range, the output should be m x n, with the entries shifted to the right:

Input Range:

A	D	F
B	E
C

Expected Output:

A	D	F
	B	E
		C

 

[Marc L]

​

Not difficult under VBA but you forgot the range address in your explanation …​

 

[mhenk]

It's in a range named "tri"

 

[Marc L]

A VBA demonstration for starters :​

Sub Demo1()
         Dim Rw As Range, C&
         Application.ScreenUpdating = False
    For Each Rw In [tri].Rows
        C = Application.CountA(Rw)
        If C < Rw.Columns.Count Then Rw.Cells(1).Resize(, C).Cut Rw.Cells(1 + Rw.Columns.Count - C)
    Next
         Application.ScreenUpdating = True
End Sub