I have an Excel table with ~800,000 lines, made in Linux and containing data about processes and CPU load. I would like to summarize the results. For example:
Let`s imagine we have the following output in column D:
empty cell
0
39
39
2
3
empty cell
Can Excel summarize the values between the empty cells / is there a function for it?
If yes, is it possible to run this for all the ~800,000 lines?
If to avoid VBA, Subtotal might just work for 800,000 rows. At the moment 1 in 6 is blank so the end result might be 800,000 + 800,000/6 (for the subtotal rows themselves) = just under the current Excel limit.
Add a helper column (say B) with a formula that returns blank only if A2 (assumes your column is labelled) is blank (assumes your data is in ColumnA), say =IF(A2="","","x")
and copy down to ~row800000.
Subtotal ColumnA:B for each change in B with your choice of function to Add subtotal to: ColumnA.
To Subtotal, select the data column and the grouping column, got to Data > Outline - Subtotal and At each change in: put the column with values, Use function: as appropriate, check or uncheck options to suit and OK.
Cross your fingers and hope the sheet is large enough!