VBA Reorganize Cohort Data

[Stephen_IV]

I over 500 models of cohort data that I need to reorganize. Please see the data below. I need to take the diagonal data and make it horizontal (row). I need to keep the original data in tact. If possible make the revision starting in column H. Thanks in advance!!!

Grade 2015-16 2016-17 2017-18 3 45% 34% 54% 4 67% 54% 47% 5 78% 65% 42% 6 65% 84% 77% 7 76% 61% 85% 8 78% 87% 73%

To this:

2015-16 2016-17 2017-18 Gr 3 0.45 0.54 0.42 Gr 4 0.67 0.65 0.77 Gr 5 0.78 0.84 0.85 Gr 6 0.65 0.61 0.73 Gr 7 0.76 0.87 Gr 8 0.78

 

[alansidman]

with VBA

Sub cohort()
Range("C2").Delete
Range("D2:D3").Delete
End Sub

 

[kweaver]

What about:

MrExcelFilterMMULT.xlsm
	A	B	C	D	E	F	G	H	I
1	Grade	2015-16	2016-17	2017-18			2015-16	2016-17	2017-18
2	3	45%	34%	54%		Gr 3	45%	0.54	0.42
3	4	67%	54%	47%		Gr 4	67%	0.65	0.77
4	5	78%	65%	42%		Gr 5	78%	0.84	0.85
5	6	65%	84%	77%		Gr 6	65%	0.61	0.73
6	7	76%	61%	85%		Gr 7	76%	0.87	0
7	8	78%	87%	73%		Gr 8	78%	0	0
Sheet1

Cell Formulas
Range	Formula
G1:I1	H1	=C1
F2:F7	F2	="Gr "&A2:A7
G2:I7	G2	=INDEX(B2:D7,{1,2,3},{1,2,3})
Dynamic array formulas.

 

[Stephen_IV]

alansidman,

Thanks that works, but I need to keep the original data state. Is there a way to do what you have given me but have it start in Column H?

 

[Stephen_IV]

kweaver, thank you! that will do the task but was trying to get VBA because of all the data that I have to work with.

 

[kweaver]

kweaver, thank you! that will do the task but was trying to get VBA because of all the data that I have to work with.

I just tried it with 600 rows of data and it was rather instantaneous.

I used 6 columns of dates as well: =INDEX(B2:G601,{1,2,3,4,5,6},{1,2,3,4,5,6})

 

[Stephen_IV]

Thank you both for your help!! Much appreciated!!!

 

[EXCEL MAX]

Here is VBA, sorry if is too late.

Sub DiagonalToHorizontal()

   Dim vNRows As Long, vNColumns As Integer
   Dim vA1, vA2, vN1 As Long, vN2 As Integer
   
   vNRows = Rows(1).Cells(Rows.Count, "A").End(xlUp).Row
   vNColumns = Columns(1).Cells(1, Columns.Count).End(xlToLeft).Column
   vA1 = Range("A1", Cells(vNRows + vNColumns - 2, vNColumns)).Value
   vA2 = vA1
   For vN1 = 2 To vNRows
      For vN2 = 2 To vNColumns
         vA2(vN1, vN2) = vA1(vN1 + vCounter, vN2)
         vCounter = vCounter + 1
      Next vN2
      vA2(vN1, 1) = "Gr " & vA1(vN1, 1)
      vCounter = 0
  Next vN1
  Range("H1").Resize(vNRows, vNColumns) = vA2
   
End Sub

 

[Stephen_IV]

EXCEL MAX thank you! Any help is much appreciated!!