Find Specific number from list of huge data

[earthworm]

I want to find a specific number that adds up to , From the list of huge combination.


The list of data can be 25000 and more and i want a small amount from the list of 25000 . it can be of any combination . I tried excel solver addon but its restricted to 100 variables . Please suggest a way
I know any solution will take lot of time but i am willing to dedicate my system for 1 hours and 2 hours etc.


Example
500
600
450
652.36
89.45

I need to identify 1152.36 from the above list . but the list is huge . >25000

 

[offthelip]

Your definition of what you want is not clear. Is this what you are trying to do?: You are given a target number and then you are trying to find a combination of numbers from the 25000 in the list which add up to that number?
If this is what you want immediately there are questions :
1: What do you want to happen if there are more than one solution?
2: How do you want the solution presented?
3: Is there any limit as to how many numbers can be added together to get the solution?
4 : Is the list of number sorted, or can we sort the numbers?
5: what happens if there is no solution

 

[Logit]

.
Disregard.

 

[earthworm]

Your definition of what you want is not clear. Is this what you are trying to do?: You are given a target number and then you are trying to find a combination of numbers from the 25000 in the list which add up to that number?
If this is what you want immediately there are questions :
1: What do you want to happen if there are more than one solution?
2: How do you want the solution presented?
3: Is there any limit as to how many numbers can be added together to get the solution?
4 : Is the list of number sorted, or can we sort the numbers?
5: what happens if there is no solution

1. Identify all solution
2. Highlight or mark X in front of each
3. No limit
4. Not sorted
5. User will resign from this painful assigned task

 

[offthelip]

I had a think about this and I made a start but I have to leave this now. however do think you can solve this problem with a brute force solution. using a reentrant routine: this is what I have got so far:

Dim inarr() As Variant
Dim lastrow As Long
Dim targetv As Variant
Dim currentv As Variant
Dim cnt As Long






Sub loopy()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 1))
targetv = Cells(2, 5)
currentv = 0
cnt = 0
Call addone


End Sub


Sub addone()
 cnt = cnt + 1
 For i = 1 To lastrow
   If currentv + inarr(i, 1) = targetv Then
    Fnd = True
    Cells(i, 2) = "*"
    Exit For
   Else
   If currentv + inarr(i, 1) > targetv Then
     ' overshot so exit
     MsgBox (currentv)
   Else
   currentv = currentv + inarr(i, 1)
    Call addone
   End If
   End If
 Next i
 
End Sub

 

[earthworm]

I had a think about this and I made a start but I have to leave this now. however do think you can solve this problem with a brute force solution. using a reentrant routine: this is what I have got so far:

Dim inarr() As Variant
Dim lastrow As Long
Dim targetv As Variant
Dim currentv As Variant
Dim cnt As Long






Sub loopy()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 1))
targetv = Cells(2, 5)
currentv = 0
cnt = 0
Call addone


End Sub


Sub addone()
 cnt = cnt + 1
 For i = 1 To lastrow
   If currentv + inarr(i, 1) = targetv Then
    Fnd = True
    Cells(i, 2) = "*"
    Exit For
   Else
   If currentv + inarr(i, 1) > targetv Then
     ' overshot so exit
     MsgBox (currentv)
   Else
   currentv = currentv + inarr(i, 1)
    Call addone
   End If
   End If
 Next i
 
End Sub

Thanks . I am not expert in macro even though i know how to use it . whatever method whether its brute force or some other logarithm i need an answer.
Please guide how to use this macro as is showing some 0 error message window.

You may restrict the solution to 2 only instead of all solution as, In 25000 data all solution will lead to another mess . Please ensure while making ,That my list is huge like 25000 and more, and my required answer / amount is hidden in some combination of that 1 huge list.

 

[earthworm]

This is the question that no one has answered in net containing that much huge list . Hence if anyone post the solution for this problem this will be a legacy! I dont mind if user has to leave system for 1 or 2 hours but solution is required as its the matter of job resignation for some needy guy in my office.

 

[jeremypyle]

Are you trying to find out which cell or cells contain the number 1152.36?

 

[earthworm]

No it's the value that will add in a list to represent my number

 

[jeremypyle]

Try inserting the following into each cell:

a1:Data
a2:500
a3:600
a4:450
a5:652.36
a6:89.45

Then in column b insert:
b1:Multiplier
b2:=a2*b2
b3:=a3*b3
b4:=a4*b4
b5:=a5*b5
b6:=a6*b6

Then in column F please put the following

f2:=1152.36
f3:=sum(C:C)
f4:=sum(f2-f3)

Once you have done this, please go to solver and do the following:

1) Set objective as:
$F$4

Value off as 0


By Changing Variable Cells:
$b$2:$b$201

Subject to the Constraints:
$b$2:$b$201<=1
$b$2:$b$201=Integer
$b$2:$b$201>=0

Uncheck "Make Unconstrained....."


Then for solving method
Simplex LP

Then click "Solve"

The numbers that add up to your number of 1152.36 will have numbers change to 1 next to them. The other numbers will show 0 next to them.

This should work