Formula to Transpose Diagonals using OFFSET

exceluser2007

Active Member
Hi All,

I have the following sample data.

Excel Workbook
BCDEFGHIJKL
3Quarter0123Sep-01Dec-01Mar-02Jun-02
4Sep-0124384878
5Dec-01223745
6Mar-022536
7Jun-0225
 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

Active Member
Running
Code:
``````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
Code:
``````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

Active Member
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

MrExcel MVP
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

Active Member
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.

1,082,106
Messages
5,363,185
Members
400,721
Latest member
eileen123

This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...