Formula to Transpose Diagonals using OFFSET

[exceluser2007]

Hi All,

I have the following sample data.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L
3	Quarter	0	1	2	3			Sep-01	Dec-01	Mar-02	Jun-02
4	Sep-01	24	38	48	78			24	22	25	25
5	Dec-01	22	37	45					38	37	36
6	Mar-02	25	36							48	45
7	Jun-02	25									78
Sheet1

Using the table on the left, I have manually created the desired table on the right.

It basically transposes the daigonals in the first triangulated table into a reflection of the original table.

I'm sure that this can be easily automated using a OFFSET formula on cell C5 (starting cell) in the left-hand table.

Could anyone please assist with this formula approach.

 

[theozz]

Running

Sub x()
Dim rng As Range
Set rng = Range("b4:b7")
Dim Answ, i As Double, j As Double
ReDim Answ(1 To 5)
For i = 1 To 5
     Answ(i) = Application.Transpose(rng.Offset(, i - 1).Value)
     Answ(i) = y(Answ(i))
Next
Answ = Application.Transpose(Application.Transpose(Answ))
Range("a21").Resize(UBound(Answ, 1), UBound(Answ, 2)) = Answ
End Sub

Sub Function

Function y(v As Variant)
Dim ii As Double
Dim tmp As Variant
Dim cnt As Double
cnt = UBound(v)
ReDim tmp(1 To UBound(v))
For ii = UBound(v) To 1 Step -1
     If CStr(v(ii)) <> "" Then
     tmp(cnt) = v(ii)
     cnt = cnt - 1
     End If
Next
y = tmp
End Function

 

[exceluser2007]

theozz, sincere thanks for your help. I appreciate it.

However, as I would like to send this worksheet to other people, who may not be familiar with VBA, i was wondering how to do it using an OFFSET formula which could be dragged across to give the reflection. of the original triangle.

Sincerely appreciate your help, but if you or any other MrExceler's could please explain how to do it using formulae that would help me with this problem, and my develop understanding of Excel in general.

Thanks,

 

[tusharm]

Good for you. No need for VBA when Excel-native solutions exist.

The solution requires knowing how many empty cells there are in each column. We'll put that information in H. So, in H4 enter the formula =COUNTIF(INDEX($C$4:$F$7,0,ROW()-ROW($H$3)),"")

Then, in I4 enter the formula =IF(COLUMN()-COLUMN($I$3)<$H4,"",INDEX($C$4:$F$7,COLUMN()-COLUMN($I$3)+1-$H4,ROW()-ROW($I$3)))

Copy I4 to J4:L4. Copy H4:L4 to rows 5:7.

theozz, sincere thanks for your help. I appreciate it.

However, as I would like to send this worksheet to other people, who may not be familiar with VBA, i was wondering how to do it using an OFFSET formula which could be dragged across to give the reflection. of the original triangle.

Sincerely appreciate your help, but if you or any other MrExceler's could please explain how to do it using formulae that would help me with this problem, and my develop understanding of Excel in general.

Thanks,

 

[exceluser2007]

Tushar, sincere thanks for your encouraging and detailed response, works great!

It is always nice to understand and appreciate the true power of Excel through such function based approaches.

Thanks again.