0

I have a series of data in Excel (280,000 lines) with random values. Each line represents the total ticket of consumptions made in a supermarket branch.

I need to select tickets from those 280,000 lines that add up to USD 1,500,000.

I thought first minimizing errors by using binaries on Solver but the variable limit was too low.

I am thinking of trying with do while / do until. How do I structure this?

Community
  • 1
  • 1
lbochitt
  • 3
  • 1

1 Answers1

0

Create a Collection called Tickets. Create a variable to hold the sum, called Total. Then you can loop down the sheet, adding tickets to the total and saving the rows in the collection until you reach 1,500,000.

Sub Example()
    Const STARTING_ROW As Long = 2 'Change this
    Const VALUE_COLUMN As Long = 5 'Change this
    
    Dim DataSheet As Worksheet
    Set DataSheet = ActiveSheet 'Change this
    
    Dim CurrentRow As Range
    Set CurrentRow = DataSheet.Rows(STARTING_ROW)
    
    Dim Tickets As New Collection, Total As Double
    While Total < 1500000
        Tickets.Add CurrentRow
        With CurrentRow.Columns(VALUE_COLUMN)
            If IsNumeric(.Value) Then Total = Total + .Value
        End With

        'Exit condition - End of Sheet
        If CurrentRow.Row = CurrentRow.Parent.Rows.Count Then 
            Exit Sub
        Else
            Set CurrentRow = CurrentRow.Offset(1)
        End If
    Wend
End Sub

At the end of this While Loop, you will have all of the ranges inside the Tickets collection, whose values sum up to at least 1,500,000.

Toddleson
  • 4,321
  • 1
  • 6
  • 26
  • Thanks! Then all i need to do is to loop through the items in the collection listing the rows in the next column so I can identify which tickets out of the 280,000 sample have been selected. Correct? – lbochitt Jun 30 '22 at 19:38
  • @lbochitt not quite sure I understand, but yes, you can find all of the Rows inside the collection. They will be Range Objects, so you can do stuff like `Tickets(1).Cells(1,1).Value` to retrieve the value in the first column of the first row in the Tickets collection. – Toddleson Jun 30 '22 at 19:41
  • Hi! Just one more question. How can I list the IDs of all the tickets that add up to the 1.5M? I can't figure out how to do a "for i" within the Tickets collection that gets me the list of the tickets selected. – lbochitt Jul 04 '22 at 13:19
  • An example, assuming ID is in the 3rd column: `For Each Ticket in Tickets: ID = Ticket.Cells(1,3).Value` or as a For Next loop, `For i = 1 to Tickets.Count: ID = Tickets(i).Cells(1,3).Value` This gets you the ID values one by one during the loop. You could then collect them into an array, or a second collection or whatever. – Toddleson Jul 04 '22 at 13:26