StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Your code is a good starting point. Couple things to speed it up.</p> <p>Instead of using ActiveCell and SelectValue just change values directly like this: </p> <pre><code>Sheet1.Cells(1, 1) = "asdf" </code></pre> <p>Also, sort your sheet on the first (key) column before you start your loops (there is a VBA Sort method if you need to do this programatically). It might take a little time but will save you in the long run. Then your Do Until IsEmpty inner loop only has to go until the value of the key changes instead of through the entire data set every time. This reduces your run time an order of magnitude.</p> <p><strong>UPDATE</strong><br> I have included some code below. It ran in about a minute for 300K random data lines. The sort took about 3 seconds. (I have a normal desktop - approx 3 years old).</p> <p>Sort in VBA as follows <code>Sheet1.Range("A1:B300000").Sort key1:=Sheet1.Range("A1")</code>. You can also replace the Range param with two Cell params (see Excel help for examples).</p> <p>Code for the processing. You might want to parameterize the sheet - I just hardcoded it for brevity.</p> <pre><code> Dim LastKey As String Dim OutColPtr As Integer Dim OutRowPtr As Long Dim InRowPtr As Long Dim CurKey As String Const KEYCOL As Integer = 1 'which col holds your "keys" Const VALCOL As Integer = 2 'which col holds your "values" Const OUTCOLSTART As Integer = 4 'starting column for output OutRowPtr = 0 'one less than the row you want your output to start on LastKey = "" InRowPtr = 1 'starting row for processing Do CurKey = Sheet2.Cells(InRowPtr, KEYCOL) If CurKey <> LastKey Then OutRowPtr = OutRowPtr + 1 LastKey = CurKey Sheet2.Cells(OutRowPtr, OUTCOLSTART) = CurKey OutColPtr = OUTCOLSTART + 1 End If Sheet2.Cells(OutRowPtr, OutColPtr) = Sheet2.Cells(InRowPtr, VALCOL) OutColPtr = OutColPtr + 1 InRowPtr = InRowPtr + 1 Loop While Sheet2.Cells(InRowPtr, KEYCOL) <> "" </code></pre>

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