title: function like solver function in excel sheet
body: <p>Greeting</p>
  <p>I have an excel sheet with 2 sheets, in the first sheet name Cost I have amounts in column A ,ID of costumers in column B and invoice number in column C, in this sheet column C is empty.</p>
  <p>The second sheet name Max I have sum of group of value from column A in firs sheet(Cost), ID number second column and invoice number in column C , in this sheet all columns are fill.</p>
  <p>I need to fill the first sheet(Cost) column C with invoice number for those group of amount had the same IDs and had the sum matched with the Column A from second sheet(Max).</p>
  <p>Noted that some of amounts have 60 field(had the same IDs) every 10 amounts matched with the sum in the second sheet (Max) column A and this sum have a one or more Invoice number so I need to put the same invoice number for every group of amounts that match the sum and second invoice number for the second group</p>
  <p>here is an example :</p>
  <p>Sheet Cost________________________________________________________________ Sheet Max</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>amounts</td>
  <td>ID</td>
  <td>Inv. Number</td>
  <td>Total</td>
  <td>ID</td>
  <td>Inv. Number</td>
  </tr>
  <tr>
  <td>25</td>
  <td>12</td>
  <td></td>
  <td>50</td>
  <td>12</td>
  <td>1AA</td>
  </tr>
  <tr>
  <td>25</td>
  <td>12</td>
  <td></td>
  <td>50</td>
  <td>12</td>
  <td>1BB</td>
  </tr>
  <tr>
  <td>45</td>
  <td>12</td>
  <td></td>
  <td>50</td>
  <td>12</td>
  <td>1CC</td>
  </tr>
  <tr>
  <td>5</td>
  <td>12</td>
  <td></td>
  <td>90</td>
  <td>88</td>
  <td>2HH</td>
  </tr>
  <tr>
  <td>5</td>
  <td>12</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>10</td>
  <td>88</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>80</td>
  <td>88</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>15</td>
  <td>12</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>5</td>
  <td>12</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>25</td>
  <td>12</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Need to fill like that :</p>
  <p>Sheet Cost_____________________________________________________________ Sheet Max</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>amounts</td>
  <td>ID</td>
  <td>Inv. Number</td>
  <td>Total</td>
  <td>ID</td>
  <td>Inv. Number</td>
  </tr>
  <tr>
  <td>25</td>
  <td>12</td>
  <td>1AA</td>
  <td>50</td>
  <td>12</td>
  <td>1AA</td>
  </tr>
  <tr>
  <td>25</td>
  <td>12</td>
  <td>1AA</td>
  <td>50</td>
  <td>12</td>
  <td>1BB</td>
  </tr>
  <tr>
  <td>45</td>
  <td>12</td>
  <td>1BB</td>
  <td>50</td>
  <td>12</td>
  <td>1CC</td>
  </tr>
  <tr>
  <td>5</td>
  <td>12</td>
  <td>1BB</td>
  <td>90</td>
  <td>88</td>
  <td>2HH</td>
  </tr>
  <tr>
  <td>5</td>
  <td>12</td>
  <td>1CC</td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>10</td>
  <td>88</td>
  <td>2HH</td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>80</td>
  <td>88</td>
  <td>2HH</td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>15</td>
  <td>12</td>
  <td>1CC</td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>5</td>
  <td>12</td>
  <td>1CC</td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>25</td>
  <td>12</td>
  <td>1CC</td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I have write this program :</p>
  <p>from itertools import count
  import random
  import pandas
  import openpyxl
  from ast import Break
  import openpyxl
  import pandas as pd
  import numpy as np</p>
  <pre><code>wb = openpyxl.load_workbook('Work.xlsx')
  cost = wb['cost']
  max = wb['max']
  
  
  counters = 0
  C3 = 0
  Collector = []
  CollectorCounter = []
  work_side1 = []
  list_Cost_amount = []
  list_CostId_value = []
  list_MaxId_value = []
  list_Max_Total = []
  list_Max_inv = []
  list_CostId_number = []
  list_MaxId_number = []
  </code></pre>
  <p>''' End define variable '''</p>
  <p>'''Pull all Data From Cost Sheet col ID'''</p>
  <pre><code>for row in range(2,1000000):
      Cost_col1 = chr(65)
      char = chr(65 + 1)
      Numb = cost[(char + str(row))].value
      ccol = cost[(Cost_col1 + str(row))].value
      id_cost = cost[(char + str(row))]
      if Numb == None:
          break
  
  
      if list_CostId_value == []:
         list_Cost_amount.append(ccol)
         list_CostId_number.append(id_cost)
         list_CostId_value.append(Numb)
         counters = counters + 1
      else:
          list_CostId_number.append(id_cost)
          list_Cost_amount.append(ccol)
          list_CostId_value.append(Numb)
          counters = counters + 1
  </code></pre>
  <p>'''End of Pull Data from Cost Sheet col ID'''</p>
  <p>'''Pull all Data From Max Sheet col ID'''</p>
  <pre><code>counters = 0
  for row in range(2,1000000):
      char = chr(65 + 1)
      char_maxcol3 = chr(65 + 2)
      max_col1 = chr(65)
      Numb = max[char + str(row)].value
      maxccol = max[max_col1 + str(row)].value
      maxcol3 = max[char_maxcol3 + str(row)].value
      id_max = max[char + str(row)]
      if Numb == None:
          break
      if list_MaxId_value == []:
         list_MaxId_number.append(id_max)
         list_Max_Total.append(maxccol)
         list_Max_inv.append(maxcol3)
         list_MaxId_value.append(Numb)
         list_MaxId_number.append(id_max)
         counters = counters + 1
      else:
          list_Max_Total.append(maxccol)
          list_MaxId_value.append(Numb)
          list_Max_inv.append(maxcol3)
          list_MaxId_number.append(id_max)
          counters = counters + 1
  </code></pre>
  <p>'''End of Pull Data from Max Sheet col ID'''</p>
  <p>''' Processing '''</p>
  <pre><code>for catch in range(0,len(list_MaxId_value)):
      char = chr(65)
      C1 = 0
      C2 = 0
      St = 0
      SumCost = 0.0
      Taken_Max_Total = 0.0
      Taken_ID_Max = list_MaxId_value[catch]
      Taken_Inv_Max = list_Max_inv[catch]
      Taken_Max_Total = list_Max_Total[catch]
      print(Taken_Max_Total)
     
      for il in range(0,len(list_CostId_value)):
          if Taken_ID_Max == list_CostId_value[il]:
              work_side1.append(list_Cost_amount[il])
              C1+=1
                 
      while(St != 1):
          
          if (SumCost + work_side1[C2]) &lt; Taken_Max_Total:
                print(work_side1[C2])
                SumCost = work_side1[C2]   + SumCost
                Collector.append(C2)
              
          if SumCost &gt; Taken_Max_Total:
                print(SumCost)
                C2 = random.randint(1,(len(work_side1)-1))
                SumCost = 0.00
                Collector.clear()
                CollectorCounter.clear()
          if SumCost == Taken_Max_Total:
                  print(len(Collector))
                  for each in range(0,len(Collector)-1):
                      cost[chr(67)+str(each)] = Taken_Inv_Max
                      print(work_side1[each])
                      list_Cost_amount.remove(work_side1[each])
                      work_side1.remove(work_side1[each])
                  Collector.clear()
                  CollectorCounter.clear()
                  St = 1
                  break
             
          if C2 == (len(work_side1)-1):
               C2 = random.randint(1,(len(work_side1)-1))
               SumCost = 0.0
               Collector.clear()
               CollectorCounter.clear()
          C2 = random.randint(1,(len(work_side1)-1))
          
          C3 = 0
          while(C3 != (len(Collector)-1)):
              if (len(Collector)-1) &lt;= 0:
                  break
              if C3 &gt; (len(Collector)-1):
                  C3 -=1
              if C2 == Collector[C3]:
                 C2 = random.randint(0,(len(work_side1)-1))
                 C3 = 0
             
              if C3 == (len(Collector)-1) &amp; C2 == Collector[C3]:
                 C2 = random.randint(1,(len(work_side1)-1))
                 C3 = 0
              if C3 == (len(Collector)-1) &amp; C2 != Collector[C3]:
                  break
              C3 +=1
              if C3 &gt; (len(Collector)-1) &amp; C2 != Collector[C3]:
                  C3 -=1
  </code></pre>
  <p>''' End of Processing '''</p>
  <pre><code>wb.save('Work.xlsx')
  </code></pre>
  <hr />
  <p>but the problem was it take a random number to calculate and compare so it take the sum of all small numbers with ID =12 then looping to find a number that equal to the total (infinity loop),
  I need to make a function like solver method in excel ( search for a sum and take all numbers that I selected ).</p>