StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Sometimes it is possible to do something once in Excel far more easily than it is to do something repeatably.</p> <p>Assuming you are just trying to get the answer once or twice, and then throw away the spreadsheet (as opposed to run it every night, or give it to someone else to run), here's how I would do it.</p> <p>I assume your raw data is in columns A, B and C, with headings in row 1, and data starting in row 2.</p> <p>Sort the table by TransId as your primary key, and Time as your secondary, both ascending. (The following won't work if this isn't done.)</p> <p>Add a new column, D, titled Duration with a formula that like this (Excel formulae haven't formatting or comments; I have added those to help explain, but they need to be stripped out):</p> <pre><code>=IF(B2=B3, // if this row's TransId is the same as the next one "", // leave this field blank C3- // else find the difference between the last timestamp and... VLOOKUP( // look for the first value A2, // matching this TransId A:C, // within the entire table, 3) // Return the value in the third column - i.e. timestamp ) </code></pre> <p>Now the data you want is in column D, but not in the format you want.</p> <p>Select Columns A-D and copy them. Use Paste Special to copy the <em>values</em> only into a new worksheet. </p> <p>Delete column B and column C in the new worksheet, so all is left is TransID and Duration. </p> <p>Sort by Duration, to bring all the rows with values next to each other. </p> <p>Sort only the rows with values by TransId. </p> <p>Voila, and there is your solution! Hope you don't need to repeat this!</p> <p>p.s. This is untested</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload