How do I collapse multiple rows into a single row in Excel 2007?

Question

I have a large Excel table (591 cols, 2645 rows) with a basic format that looks something like this:

| Time | Data 1 | Data 2 | Data 3 | Data 4 | Data 5 | Data 6 | Data 7 |
|======+========+========+========+========+========+========+========|
| 0.01 |  0.35  |        |        |        |        | 0.1351 | 0.2398 | 
| 0.02 |        |  0.42  |        |        |        | 0.4314 | 0.4342 | 
| 0.03 |        |        |  0.99  |        |        | 0.3414 | 0.4321 |
| 0.04 |        |        |        |  0.12  |        | 0.4351 | 0.4256 |
| 0.05 |        |        |        |        |  0.66  | 0.7894 | 0.9874 |

This is basically a recording of a data stream where some fields are sampled only once every time step, while others are sampled on every individual time step. An entire "record", then, is recorded once the loop is finished (ie, "Data 1" is written again).

The final data record, for the purposes of data processing and analysis, looks something like this:

| Time | Data 1 | Data 2 | Data 3 | Data 4 | Data 5 | Data 6 | Data 7 |
|======+========+========+========+========+========+========+========|
| 0.05 |  0.35  |  0.42  |  0.99  |  0.12  |  0.66  | 0.7894 | 0.9874 | 

Notice that the timestamp is equal to a timestamp found in the table, that the recurring data fields are equal to the data values at that time, and that the periodic data fields are equal to the last reported value for each of those fields.

The record for a single loop then would basically consist of the final value recorded for any field within the given timeframe. I can do this easily for a single step of this, but I have 2600+ row of data to process per data set and half a dozen data sets to process.

Is there a clean/simple/pragmatic way to do this across the entire data file? I could brute force this any number of ways, but I'm hoping to not have to reinvent the wheel. If I could write the output to a new worksheet, that'd be great.

Answer 1

Welcome to SO.

I have a small solution based on cycles of length 5. It uses the indirect function, and you should be able to extend it as needed.

First, I added some rows to your example, to get up to time 0.15. This takes up a1 through h16. Then I added i17 to j19, to tell which rows to examine. I made these:

 2  6
 7 11
12 16

This means that i17 and j17 represent rows 2 through 6 (or cycle 1 in your data). I would recommend that you make j17 = i17+4, and j18=i17+1.

OK, here comes the indirect function. In a17, =INDIRECT("A"&$J17), which means, give me col A, row 2 (because there is a 2 in cell i17. The $ means use an absolute column, and keep on using I in the formula).

In b17, =SUM(INDIRECT("b"&$I17&":b"&"$"&$J17)), which means sum of b2 through b6.

In c17, =SUM(INDIRECT("c"&$I17&":c"&"$"&$J17)).

Repeat the pattern for d17, e17, and f17.

In g17, you just want to copy what is in g6 with =INDIRECT("g"&$J17).

In h17, you copy what is in h6 with =INDIRECT("h"&$J17).

You may then copy the formulas from a17:h17 down three rows.

Answer 2

Something like this might work for you

Sub tester()
    SummarizeRows ThisWorkbook.Sheets("Sheet1").Range("A1"), _
                  ThisWorkbook.Sheets("Sheet2").Range("A1")
End Sub


Sub SummarizeRows(rngIn As Range, rngOut As Range)

Const MASTER_COL As Long = 2 'summarize when new value here (except first row)

Dim vals(), data
Dim numRows As Long, numCols As Long
Dim r As Long, c As Long, r_out As Long
Dim c2 As Long, v

    r_out = 1
    'get all the input data
    data = rngIn.CurrentRegion.Value
    numRows = UBound(data, 1)
    numCols = UBound(data, 2)
    ReDim vals(1 To numCols)

    For r = 2 To numRows
        For c = 1 To numCols
            v = data(r, c)
            If Len(v) > 0 Then
                If c = MASTER_COL And r > 2 Then
                    'new value in "master" column...
                    r_out = r_out + 1
                    For c2 = 1 To numCols
                        data(r_out, c2) = vals(c2)
                        vals(c2) = ""
                    Next c2
                End If
                vals(c) = v
            End If
        Next
    Next r

    'write any last values
    r_out = r_out + 1
    For c2 = 1 To numCols
        data(r_out, c2) = vals(c2)
    Next c2

    rngOut.Resize(r_out, numCols).Value = data

End Sub