How can we change data from a 2D matrix to a 1D matrix?-CodePudding

I am trying to list data points, which are in cells FQ121 to FW151 (2D matrix) and I would like to list everything in one column (1D matrix). I have an 'Output' sheet where I am trying to show my results and a 'Daily Forecast' sheet where the data sits. Again, how can I show, in one column in my 'Output' sheet, the range of values from FQ121 to FW151, which is 31 rows by 7 columns. I need to use functions and not VBA code, as the client will probably not understand any kind of VBA solution. Thanks!

CodePudding user response：

Using INDEX and SEQUENCE:

By Row:

=INDEX(FQ121:FW151,SEQUENCE(31*7,,1,1/7),MOD(SEQUENCE(31*7,,0),7) 1)

By Column:

=INDEX(FQ121:FW151,MOD(SEQUENCE(31*7,,0),31) 1,SEQUENCE(31*7,,1,1/31))

CodePudding user response：

Consider this example, where the table under A1 is flattened by rows on F1 and by columns on H1

And the code that does the work

Public Sub T()

    FlattenByRows Range("A1:D6"), Range("F1")
    
    FlattenByColumns Range("A1:D6"), Range("H1")

End Sub


Public Sub FlattenByColumns(r_table As Range, r_target As Range)
    Dim n As Long, m As Long, i As Long, j As Long
    n = r_table.Rows.Count: m = r_table.Columns.Count
    
    i = 1
    For j = 1 To m
        r_target.Cells(i, 1).Resize(n, 1).Value2 = _
            r_table.Cells(1, j).Resize(n, 1).Value2
        i = i   n
    Next j
    
End Sub

Public Sub FlattenByRows(r_table As Range, r_target As Range)
    Dim n As Long, m As Long, i As Long, j As Long
    n = r_table.Rows.Count: m = r_table.Columns.Count
    
    j = 1
    For i = 1 To n
        r_target.Cells(j, 1).Resize(m, 1).Value2 = _
            WorksheetFunction.Transpose( _
                r_table.Cells(i, 1).Resize(1, m).Value2)
        j = j   m
    Next i
End Sub