Geek asks and answers

How to collapse multiple rows into one row in Excel 2007?

I have a large Excel spreadsheet (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 data stream record in which some fields are selected only once at each step, while others are selected at each individual time step. Then, the entire record is recorded after the end of the cycle (ie, "Data 1" is written again).

The last data record for data processing and analysis purposes 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 |

Note that the timestamp is equal to the timestamp found in the table, that the repeated 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 these fields.

, . , 2600 + .

// ? , . , .

+3
4

, , ...

( 2), , ( 1). 1 , . :

=IF(Sheet1!A2="",0,Sheet1!A2)

, 1 2 2. , B3 ( , ):

=IF(Sheet1!B3="",B2,Sheet1!B3)

2, , 1. :

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

.

+1

SO.

5. indirect, .

-, , 0,15. a1 h16. i17 j19, , . :

 2  6
 7 11
12 16

, i17 j17 2 6 ( 1 ). j17 = i17 + 4, j18 = i17 + 1.

, . a17, =INDIRECT("A"&$J17), , col A, 2 ( i17 2. $ ).

b17, =SUM(INDIRECT("b"&$I17&":b"&"$"&$J17)), b2 b6.

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

d17, e17 f17.

g17 , g6, =INDIRECT("g"&$J17).

h17 , h6, =INDIRECT("h"&$J17).

a17: h17 .

+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
+2
source

Paste this into the cells of the row below your data. A is the column that you insert in

=LOOKUP(2,1/(A1:A5<>""),A:A)
0
source

More articles:


All Articles