title: Extract data from a list until a specified total is met
body: <p>I have 2 lists of data that are stacked on top of eachother. These are lists of accounts where I need the total of the top list to be as close as possible to the number specified on the Master sheet in cell I10. I have been doing this manually over the last little while. For example, if I need the sum of all the accounts to be as close to 1 billion as I can get, I start copying and pasting out values from the top list and place them in the bottom list until the sum of the top list is about 1 billion. I originally wrote some vb code that just keeps taking account values from the bottom of list one and places them a the bottom of list 2 until the sum is below 1 billion, but the issue with this was that the last number that gets pulled from list 1 is ridiculously large, so my total is very far under my 1 billion range. </p>
  
  <p>My new approach (which is probably very poor) was to first do as above, then search through the bottom list for smallish values and place them back in the top list until I get fairly close to 1 billion. (I can be over or under 1 billion, but not by an extreme amount) Oh also, my lists are aways changing size.. I.e this needs to be dynamic (and list 2 has the possibility of blank cells)</p>
  
  <p>Below is my first attempt that made my total too small.</p>
  
  <p>If someone can help me figure out how to do this I would be very appreciative.</p>
  
  <pre><code>    If Worksheets("Output").Range("B1").End(xlDown).Offset(2) &gt; (Worksheets("Master Sheet").Range("I10").Value + 0.1) * 1000000 Then
          Do
              Worksheets("Output").Range("A1").End(xlDown).Select
              Range(ActiveCell, ActiveCell.End(xlToRight).Offset(, 3)).Cut
              Worksheets("Output").Range("A65000").End(xlUp).Offset(-1).Select
              Selection.Insert Shift:=xlDown
          Loop Until Worksheets("Output").Range("B1").End(xlDown).Offset(2) &lt; (Worksheets("Master Sheet").Range("I10") + 0.1) * 1000000
      End If
  </code></pre>