Formula to Transpose Diagonals using OFFSET

exceluser2007

Active Member
Joined
Nov 21, 2007
Messages
365
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
Joined
Jun 11, 2007
Messages
328
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
Joined
Nov 21, 2007
Messages
365
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
Joined
May 28, 2002
Messages
11,007
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
Joined
Nov 21, 2007
Messages
365
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.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top