help require for algebra function?? - Microsoft Access / VBA

Norman Fritag

Hi there
I need some help, to matching data!
eg: out of 20 elements: eg (10,2,4 17,24,5,30, 40, 50, 100, 23, 35, 200,
3501, 201, 245, 323,2000, 33, 44,265,etc)
I would want to know which of these elements eg: make up the sum 275??
in this example the combination: 245,30 = 275 ; 200,24,50 =275;
200,30,40,5 = 275 and so forth
Would I have to use vba code function and or could I us as well sql?

any hints are much appreciated

Regards

Norman

Feb 22 '06 #1

Subscribe Post Reply

1193

Terry Kreft

My first thought is there are 20! combinations here which is (according to
calc <g>) 405483668029439994.

So good luck with that.

--

Terry Kreft
"Norman Fritag" <mu*****@ozemail.com.au> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...

Hi there
I need some help, to matching data!
eg: out of 20 elements: eg (10,2,4 17,24,5,30, 40, 50, 100, 23, 35, 200,
3501, 201, 245, 323,2000, 33, 44,265,etc)
I would want to know which of these elements eg: make up the sum 275??
in this example the combination: 245,30 = 275 ; 200,24,50 =275;
200,30,40,5 = 275 and so forth
Would I have to use vba code function and or could I us as well sql?

any hints are much appreciated

Regards

Norman

Feb 22 '06 #2

Anthony England

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:ZU********************@karoo.co.uk...

My first thought is there are 20! combinations here which is (according to
calc <g>) 405483668029439994.

So good luck with that.

--

Terry Kreft

Are you sure that's correct? Each of the elements can appear either 1 or 0
times so we've only got to check 2^20 sets.

"Norman Fritag" <mu*****@ozemail.com.au> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
Hi there
I need some help, to matching data!
eg: out of 20 elements: eg (10,2,4 17,24,5,30, 40, 50, 100, 23, 35, 200,
3501, 201, 245, 323,2000, 33, 44,265,etc)
I would want to know which of these elements eg: make up the sum 275??
in this example the combination: 245,30 = 275 ; 200,24,50 =275;
200,30,40,5 = 275 and so forth
Would I have to use vba code function and or could I us as well sql?

any hints are much appreciated

Regards

Norman

Feb 22 '06 #3

Anthony England

"Anthony England" <ae******@oops.co.uk> wrote in message
news:dt**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:ZU********************@karoo.co.uk...
My first thought is there are 20! combinations here which is (according
to
calc <g>) 405483668029439994.

So good luck with that.

--

Terry Kreft

Are you sure that's correct? Each of the elements can appear either 1 or
0 times so we've only got to check 2^20 sets.

"Norman Fritag" <mu*****@ozemail.com.au> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
Hi there
I need some help, to matching data!
eg: out of 20 elements: eg (10,2,4 17,24,5,30, 40, 50, 100, 23, 35, 200,
3501, 201, 245, 323,2000, 33, 44,265,etc)
I would want to know which of these elements eg: make up the sum 275??
in this example the combination: 245,30 = 275 ; 200,24,50 =275;
200,30,40,5 = 275 and so forth
Would I have to use vba code function and or could I us as well sql?

any hints are much appreciated

Regards

Norman

As nobody else seems to want to have a go, here is a first attempt. It
might be possible to optimize but I suppose it depends whether current
performance is acceptable. With the example you gave, I found 103 matches
in 5.89 seconds which may be acceptable to you, but just note that I wrote
this bit of code on a whim and haven't had time to prove to myself that all
matches will be found. However, it does seem to work Comments anyone?
' *****************************
' Paste all this in a new module
' *****************************
Option Compare Database
Option Explicit

Public Sub DoTest()

On Error GoTo Err_Handler

Dim strSeries As String
Dim lngSum As Long
Dim strAllMatches As String
Dim astrSeries() As String
Dim astrNumbers() As String
Dim strTemp As String
Dim lngMatchCount As Long
Dim sngTime As Single
Dim lngX As Long
Dim lngY As Long

sngTime = Timer

strSeries = "10,2,4,17,24,5,30,40,50," & _
"100,23,35,200,3501,201," & _
"245,323,2000,33,44"

lngSum = 275

strAllMatches = GetAllMatches(lngSum, strSeries)

astrSeries() = Split(strAllMatches, ";")

For lngX = LBound(astrSeries) To UBound(astrSeries)

strTemp = CStr(lngSum) & " = "

astrNumbers = Split(astrSeries(lngX), ",")

For lngY = LBound(astrNumbers) To UBound(astrNumbers)
strTemp = strTemp & astrNumbers(lngY) & " + "
Next lngY

strTemp = Left$(strTemp, Len(strTemp) - 3)

lngMatchCount = lngMatchCount + 1

Debug.Print strTemp

Next lngX

sngTime = Timer - sngTime

MsgBox CStr(lngMatchCount) & " matches found in " & _
CStr(sngTime) & " seconds", vbInformation

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
Private Function GetAllMatches(lngTargetSum As Long, strSeries As String) As
String

Dim strReturn As String
Dim astrValues() As String
Dim alngValues() As Long
Dim strValue As String
Dim lngCount As Long
Dim lngMax As Long

astrValues = Split(strSeries, ",")

ReDim alngValues(LBound(astrValues) To UBound(astrValues))

For lngCount = LBound(astrValues) To UBound(astrValues)
alngValues(lngCount) = CLng(astrValues(lngCount))
Next lngCount

lngMax = (2 ^ (UBound(alngValues) + 1)) - 1

For lngCount = 0 To lngMax
strSeries = GetMatches(lngCount, lngTargetSum, alngValues)
If Len(strSeries) > 0 Then
strReturn = strReturn & ";" & strSeries
End If
Next lngCount

If Len(strReturn) > 1 Then
strReturn = Mid$(strReturn, 2)
End If

GetAllMatches = strReturn

End Function

Private Function GetMatches(lngBits As Long, _
lngTarget As Long, _
aNumbers() As Long) As String

Dim lngCount As Long
Dim lngSum As Long
Dim strReturn As String

For lngCount = 0 To UBound(aNumbers)
If (2 ^ lngCount And lngBits) = 0 Then
lngSum = lngSum + aNumbers(lngCount)
If lngSum > lngTarget Then
Exit For
End If
End If
Next lngCount

If lngSum = lngTarget Then
For lngCount = 0 To UBound(aNumbers)
If (2 ^ lngCount And lngBits) = 0 Then
strReturn = strReturn & "," & CStr(aNumbers(lngCount))
End If
Next lngCount
If Len(strReturn) > 1 Then
strReturn = Mid$(strReturn, 2)
End If
End If

GetMatches = strReturn

End Function
' *************************
' Code End
' *************************

Results from debug window:
275 = 40 + 100 + 23 + 35 + 33 + 44
275 = 10 + 30 + 100 + 23 + 35 + 33 + 44
275 = 10 + 2 + 4 + 24 + 100 + 23 + 35 + 33 + 44
275 = 4 + 17 + 24 + 5 + 40 + 50 + 23 + 35 + 33 + 44
275 = 10 + 4 + 17 + 24 + 5 + 30 + 50 + 23 + 35 + 33 + 44
275 = 2 + 4 + 17 + 40 + 100 + 35 + 33 + 44
275 = 4 + 24 + 5 + 30 + 100 + 35 + 33 + 44
275 = 10 + 2 + 4 + 17 + 30 + 100 + 35 + 33 + 44
275 = 10 + 4 + 24 + 5 + 30 + 40 + 50 + 35 + 33 + 44
275 = 2 + 17 + 24 + 30 + 40 + 50 + 35 + 33 + 44
275 = 5 + 30 + 40 + 100 + 23 + 33 + 44
275 = 2 + 4 + 24 + 5 + 40 + 100 + 23 + 33 + 44
275 = 10 + 2 + 4 + 24 + 5 + 30 + 100 + 23 + 33 + 44
275 = 4 + 17 + 24 + 30 + 100 + 23 + 33 + 44
275 = 10 + 4 + 17 + 24 + 30 + 40 + 50 + 23 + 33 + 44
275 = 2 + 17 + 24 + 5 + 50 + 100 + 33 + 44
275 = 2 + 4 + 17 + 5 + 30 + 40 + 100 + 33 + 44
275 = 4 + 24 + 30 + 40 + 100 + 33 + 44
275 = 10 + 2 + 17 + 24 + 5 + 40 + 100 + 33 + 44
275 = 2 + 5 + 23 + 201 + 44
275 = 30 + 201 + 44
275 = 2 + 4 + 24 + 201 + 44
275 = 2 + 24 + 5 + 200 + 44
275 = 10 + 4 + 17 + 200 + 44
275 = 2 + 4 + 17 + 50 + 100 + 23 + 35 + 44
275 = 4 + 24 + 5 + 40 + 100 + 23 + 35 + 44
275 = 10 + 2 + 4 + 17 + 40 + 100 + 23 + 35 + 44
275 = 10 + 4 + 24 + 5 + 30 + 100 + 23 + 35 + 44
275 = 2 + 17 + 24 + 30 + 100 + 23 + 35 + 44
275 = 10 + 2 + 17 + 24 + 30 + 40 + 50 + 23 + 35 + 44
275 = 2 + 4 + 40 + 50 + 100 + 35 + 44
275 = 10 + 2 + 4 + 30 + 50 + 100 + 35 + 44
275 = 17 + 24 + 5 + 50 + 100 + 35 + 44
275 = 4 + 17 + 5 + 30 + 40 + 100 + 35 + 44
275 = 2 + 24 + 30 + 40 + 100 + 35 + 44
275 = 10 + 17 + 24 + 5 + 40 + 100 + 35 + 44
275 = 2 + 4 + 17 + 5 + 30 + 50 + 100 + 23 + 44
275 = 4 + 24 + 30 + 50 + 100 + 23 + 44
275 = 10 + 2 + 17 + 24 + 5 + 50 + 100 + 23 + 44
275 = 10 + 2 + 4 + 17 + 5 + 30 + 40 + 100 + 23 + 44
275 = 10 + 4 + 24 + 30 + 40 + 100 + 23 + 44
275 = 2 + 4 + 5 + 30 + 40 + 50 + 100 + 44
275 = 10 + 2 + 24 + 5 + 40 + 50 + 100 + 44
275 = 17 + 24 + 40 + 50 + 100 + 44
275 = 10 + 17 + 24 + 30 + 50 + 100 + 44
275 = 2 + 4 + 35 + 201 + 33
275 = 2 + 4 + 5 + 30 + 201 + 33
275 = 10 + 2 + 24 + 5 + 201 + 33
275 = 17 + 24 + 201 + 33
275 = 2 + 5 + 35 + 200 + 33
275 = 10 + 4 + 5 + 23 + 200 + 33
275 = 2 + 17 + 23 + 200 + 33
275 = 2 + 40 + 200 + 33
275 = 10 + 2 + 30 + 200 + 33
275 = 4 + 30 + 50 + 100 + 23 + 35 + 33
275 = 10 + 2 + 17 + 5 + 50 + 100 + 23 + 35 + 33
275 = 10 + 24 + 50 + 100 + 23 + 35 + 33
275 = 10 + 4 + 30 + 40 + 100 + 23 + 35 + 33
275 = 10 + 2 + 5 + 40 + 50 + 100 + 35 + 33
275 = 17 + 40 + 50 + 100 + 35 + 33
275 = 10 + 17 + 30 + 50 + 100 + 35 + 33
275 = 10 + 2 + 4 + 17 + 24 + 50 + 100 + 35 + 33
275 = 24 + 5 + 40 + 50 + 100 + 23 + 33
275 = 10 + 2 + 17 + 40 + 50 + 100 + 23 + 33
275 = 10 + 24 + 5 + 30 + 50 + 100 + 23 + 33
275 = 17 + 5 + 30 + 40 + 50 + 100 + 33
275 = 2 + 4 + 17 + 24 + 5 + 40 + 50 + 100 + 33
275 = 10 + 2 + 4 + 17 + 24 + 5 + 30 + 50 + 100 + 33
275 = 2 + 5 + 23 + 245
275 = 30 + 245
275 = 2 + 4 + 24 + 245
275 = 10 + 2 + 4 + 23 + 35 + 201
275 = 4 + 5 + 30 + 35 + 201
275 = 10 + 24 + 5 + 35 + 201
275 = 2 + 4 + 5 + 40 + 23 + 201
275 = 10 + 2 + 4 + 5 + 30 + 23 + 201
275 = 4 + 17 + 30 + 23 + 201
275 = 10 + 17 + 24 + 23 + 201
275 = 2 + 17 + 5 + 50 + 201
275 = 24 + 50 + 201
275 = 4 + 30 + 40 + 201
275 = 10 + 2 + 17 + 5 + 40 + 201
275 = 10 + 24 + 40 + 201
275 = 10 + 2 + 5 + 23 + 35 + 200
275 = 17 + 23 + 35 + 200
275 = 40 + 35 + 200
275 = 10 + 30 + 35 + 200
275 = 10 + 2 + 4 + 24 + 35 + 200
275 = 2 + 50 + 23 + 200
275 = 10 + 2 + 40 + 23 + 200
275 = 17 + 5 + 30 + 23 + 200
275 = 2 + 4 + 17 + 24 + 5 + 23 + 200
275 = 5 + 30 + 40 + 200
275 = 2 + 4 + 24 + 5 + 40 + 200
275 = 10 + 2 + 4 + 24 + 5 + 30 + 200
275 = 4 + 17 + 24 + 30 + 200
275 = 10 + 17 + 40 + 50 + 100 + 23 + 35
275 = 2 + 4 + 17 + 24 + 30 + 40 + 100 + 23 + 35
275 = 4 + 17 + 24 + 5 + 40 + 50 + 100 + 35
275 = 10 + 4 + 17 + 24 + 5 + 30 + 50 + 100 + 35
275 = 10 + 17 + 5 + 30 + 40 + 50 + 100 + 23
275 = 10 + 2 + 4 + 17 + 24 + 5 + 40 + 50 + 100 + 23
275 = 10 + 4 + 17 + 24 + 30 + 40 + 50 + 100

Feb 22 '06 #4

Anthony England

"Norman Fritag" <mu*****@ozemail.com.au> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...

Hi there
I need some help, to matching data!
eg: out of 20 elements: eg (10,2,4 17,24,5,30, 40, 50, 100, 23, 35, 200,
3501, 201, 245, 323,2000, 33, 44,265,etc)
I would want to know which of these elements eg: make up the sum 275??
in this example the combination: 245,30 = 275 ; 200,24,50 =275;
200,30,40,5 = 275 and so forth
Would I have to use vba code function and or could I us as well sql?

any hints are much appreciated

Regards

Norman

Feb 22 '06 #5

Terry Kreft

Nope, that's why I said it was a first thought <g>.

--

Terry Kreft
"Anthony England" <ae******@oops.co.uk> wrote in message
news:dt**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:ZU********************@karoo.co.uk...
My first thought is there are 20! combinations here which is (according to calc <g>) 405483668029439994.

So good luck with that.

--

Terry Kreft

Are you sure that's correct? Each of the elements can appear either 1 or

0 times so we've only got to check 2^20 sets.

"Norman Fritag" <mu*****@ozemail.com.au> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
Hi there
I need some help, to matching data!
eg: out of 20 elements: eg (10,2,4 17,24,5,30, 40, 50, 100, 23, 35, 200, 3501, 201, 245, 323,2000, 33, 44,265,etc)
I would want to know which of these elements eg: make up the sum 275??
in this example the combination: 245,30 = 275 ; 200,24,50 =275;
200,30,40,5 = 275 and so forth
Would I have to use vba code function and or could I us as well sql?

any hints are much appreciated

Regards

Norman

Feb 24 '06 #6

Similar topics

Help with Jack's FormMail

by: mcp6453 | last post by:

I am trying to use Jack's FormMail script (http://www.dtheatre.com/scripts/formmail). Since I'm brand new at PHP and not very good at HTML, I have an easy question, which I will narrow down. When...

PHP

computer algebra packages

by: Rahul | last post by:

Hi. Well is there an open source computer algebra system written in python or at least having a python interface? I know of 2 efforts: pythonica and pyginac...are there any others? rahul

Python

relational algebra question - please help!

by: | last post by:

I need to write a relational algebra query for the following: Show the item_ID, item description and category description for all items where the supplier uses "rail" as the delivery_method. ...

PostgreSQL Database

Cannot use mail() in IE, only works in a debugger--help

by: baustin75 | last post by:

Posted: Mon Oct 03, 2005 1:41 pm Post subject: cannot mail() in ie only when debugging in php designer 2005 -------------------------------------------------------------------------------- ...

PHP

need help urgently

by: desperadou | last post by:

hi to all experts, i am working on search engine program, and i need an sql statement that i found hard to do. table student: student_name course_name data: mike algebra...

Microsoft Access / VBA

numpy help

by: Chris Smith | last post by:

Howdy, I'm a college student and for one of we are writing programs to numerically compute the parameters of antenna arrays. I decided to use Python to code up my programs. Up to now I haven't...

Python

xslt looping help :-( please

by: tkahn6 | last post by:

Hi! Ok so im having a very very rookie (i assume) problem. I want to take this: <?xml version="1.0" encoding="ISO-8859-1"?> <?xml-stylesheet type="text/xsl" href="xslTest.xsl"?> <dataTree> ...

XML

Relational Algebra & SQL newbie needs help!

by: Metallicat | last post by:

I have been posed and attempted to answer several questions but I do not seem to be getting anywhere. I have emailed my tutor a week ago and he has not responded, If anyone can take a look and help...

Microsoft SQL Server

Algebra

by: Beany | last post by:

Hi, algebra: z = pr%q+w/x - y Perl code for this is: $z = $p * $r % $q + $w / $x - $y; Can someone please explain to me : in what order does Perl calculate the operators? What would be...

Perl

Wordpress or something else?

by: Faith0G | last post by:

I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

Content Management Systems

One-click Importing Excel Data into a*Database

by: ryjfgjl | last post by:

In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...

Microsoft Excel

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware