get all combination of a set of numbers (1 Viewer)

lala · Feb 19, 2011

i don't need all possible combination in the math sense, for my purposes 1234 and 4321 is the same thing, i'd only want to see a combination of 1,2,3,4 once, no matter which order

here's what i'm trying to do. (also, this is not a database, it's an automation tool for a task (to avoid suggestions to normalize my data)))))))))))

this tool matches one table to another using a number of fields. there're 10 fields all together but from a project to project only some fields are present. so today i'm only matching on 7 fields, tomorrow on 10, some other day on 5.
the matching goes like this. if there're 7 fields available - then first i try all 7, then all combinations of 6, all combinations of 5, all combinations of 4, all combinations of 3 and a few combinations of 2 (i will hand pick them so i don't care about them)

so for example, below are sample fields

101. Name
102. Address
103. CityState (to me they're 1)
104. ZIP
105. SSN
106. DOB
107. TIN

first i match on all - no problem, then on 6. how do i get all combinations of 6, like this
1. Name, Address, CityState, ZIP, SSN, DOB
2. Name, Address, CityState, ZIP, SSN, TIN
3. Name, Address, CityState, TIN, SSN, DOB
4. Name, Address, TIN, ZIP, SSN, DOB
and so on

i have numbers assigned to them, that's what the 101, 102, 103, etc, thought it'd make it easier

thank you!!

the_net_2.0 · Feb 19, 2011

this post is incredibly unclear, but what you want is called permuting a value or string. for instance, take a look at the attached sample. That's a basic function that loops within itself to do just this task.

Regardless of your inputs, outputs, and scenarios, what your asking will more than likely use that same code to some degree.

this, I (think) ignores repeated letters in strings, but I don't know for sure.

vbaInet · Feb 19, 2011

I think this is Combinations not Permutations because the order doesn't matter and repetition is not allowed.

You should be able to find VBA code by Googling. This isn't a straightforward code, unless someone has already written code for this and would like to share.

Brianwarnock · Feb 19, 2011

A couple of questions , not that I'm likely to have time to write the code, but
1) What do you mean by fields being available, why wont they be.

2) in your sample 7 if say TIN doesn't match due you still try a combination of 6 etc till you get a match?

My thinking is along these lines If all 10 fields exist but not all have entries, and this is what causes the change in number compared on, then first find the empty fields and then compare on the rest.

Brian

lala · Feb 19, 2011

Brianwarnock said:
A couple of questions , not that I'm likely to have time to write the code, but
1) What do you mean by fields being available, why wont they be.

2) in your sample 7 if say TIN doesn't match due you still try a combination of 6 etc till you get a match?

My thinking is along these lines If all 10 fields exist but not all have entries, and this is what causes the change in number compared on, then first find the empty fields and then compare on the rest.

Brian

1. they're empty. i get files to match and with each project there are different fields available. so i still have those fields in the master table, they're just blank

2. any time one line matches - it gets marked, but i will keep trying all combinations anyway for all the lines not matched yet.

3. i do that, i find the fields that are not blank and only work with those. but still didn't know what to do and thank you))))))))))

lala · Feb 19, 2011

the_net_2.0 said:
this post is incredibly unclear, but what you want is called permuting a value or string. for instance, take a look at the attached sample. That's a basic function that loops within itself to do just this task.

Regardless of your inputs, outputs, and scenarios, what your asking will more than likely use that same code to some degree.

this, I (think) ignores repeated letters in strings, but I don't know for sure.

thank you for the example. i am able to get this in access, that's why i specified in the beginning that i don't need ALL combinations in the math sense of it, to me 1234 and 4321 is the same things and they shouldn't be repeated.
i don't know how to explain it any better, i need all combinations but as long as the combination of numbers is present - it shouldnt be given again even with numbers in a different order

lala · Feb 19, 2011

vbaInet said:
I think this is Combinations not Permutations because the order doesn't matter and repetition is not allowed.

You should be able to find VBA code by Googling. This isn't a straightforward code, unless someone has already written code for this and would like to share.

i just saw this, thank you, that's what i'm trying to say))))))))) i tried googling and no luck, permutations are everywhere, but ot what i need. i will keep trying this myself, just thought maybe someone already has something here

lala · Feb 19, 2011

the_net_2.0 said:
Regardless of your inputs, outputs, and scenarios, what your asking will more than likely use that same code to some degree.

this, I (think) ignores repeated letters in strings, but I don't know for sure.

but you're right, i'm going to study this code and try to see what it is thats making it give me all the possible combinations and see if i can change it. will post back if i do
thank you both of you!!

the_net_2.0 · Feb 19, 2011

lala said:
but you're right, i'm going to study this code and try to see what it is thats making it give me all the possible combinations and see if i can change it. will post back if i do
thank you both of you!!

the code is extremely algorythmic in nature. it's almost impossible to interpret and follow, unless you're Mark Zuckerburg-type intelligence. =)

as far as using a combination once, that's actually going to be much easier than the sample I gave you.

And iNet, I also have a file that does allow repeats, which I call "long permutes". I think that's probably what is "right", technically, but I don't know the difference.

lala · Feb 19, 2011

the_net_2.0 said:
the code is extremely algorythmic in nature. it's almost impossible to interpret and follow, unless you're Mark Zuckerburg-type intelligence. =)

as far as using a combination once, that's actually going to be much easier than the sample I gave you.

And iNet, I also have a file that does allow repeats, which I call "long permutes". I think that's probably what is "right", technically, but I don't know the difference.

the one you gave me allows repeats, so then what does the other one do? and yes, i saw that it's using a fomula of some sort, i was going to go pick my father's brain today, he was in a math major. i suck at math)))))))))
i thought my father can explain what the formula does and i was hoping that i'd be able to go from there.
you just killed my dreams)))))))))))

lala · Feb 19, 2011

lala said:
thank you both of you!!

i meant all of you, i told you i didn't know how to count))))))))))))))

the_net_2.0 · Feb 19, 2011

lala said:
the one you gave me allows repeats, so then what does the other one do? and yes, i saw that it's using a fomula of some sort, i was going to go pick my father's brain today, he was in a math major. i suck at math)))))))))
i thought my father can explain what the formula does and i was hoping that i'd be able to go from there.
you just killed my dreams)))))))))))

whereas this one doesn't allow repeated letters, I think the other DOES. but neither does what you're looking for. that's much more simplified.

let me ask you this. if you had the following numbers:

Code:

1 2 3

from what I've read, you apparently would only want the following as an output?

1
2
3
12
23
13

right? or does each output line HAVE TO contain all of the numbers? I understand that you don't want a repeated combination, regardless of the ordering of the numbers. e.g. - "1 2" is the same as "2 1". Right? Is this spot on? or am I missing something? If this IS right on, I can modify that code right now to do what is needed.

lala · Feb 19, 2011

this is exactly it with some notes. i won't go down to 1 or 2 numbers, i will stop at 3. if it's not matching on any combinations of 3 fields - i'm done.

dying to see what you're going to do, i'm truly stuck. usually i at least have an idea on how to do it if not in the prettiest way, but not this time.

the_net_2.0 · Feb 19, 2011

why don't you give an example of a group of inputs and then the group of outputs that the code would otherwise give you? OK?

i won't be trying this for a while, because I've got appointments today.

gemma-the-husky · Feb 19, 2011

here's a recursive algorithm that will generate the ordered permutations as an array of strings. using recursion is always interesting, but fiddly to get right - I had a few goes at this! Recursion offers a very "light" code structure, but it's not iterative, and it's hard to break out - you just have to let it finish!

so for perming 5 from 7, it generates 21 strings
12345
12346
12347 etc etc

Note that if you use recursion too deeply, you can fill the stack and crash the programe.

the number of unordered perms is given by

n!
-----
r! (n-r)!

where you are lookng to select r items from a population of n
! is factorial

so for 2 (or 5) items from 7

its 7! - ( which actually simplifies to (7x6) / 2 = 21)
-----
2! x 5!

for 3 (or 4) from 7 its 7x6x5 / 1x2x3 which is 35

so set the parameters you want in the sub main, and this then calls the recursive function "combine" to fill the array. combine doesn't have to return anything, so it could be a sub in this case.

Hope you find it useful for what you want to do

Code:

Option Compare Database
Option Explicit

Dim result(1000) As String
Dim maxresult As Long

Function combine(required As Long, max As Long, combo As String) As String
Dim x As Long
Dim startfrom As Long
Dim newstr As String

If Len(combo) = required Then
    maxresult = maxresult + 1
    result(maxresult) = combo
    Exit Function
End If
    
If Len(combo) = 0 Then
    startfrom = 0
Else
    startfrom = CLng(Right(combo, 1))
End If


For x = startfrom + 1 To ((max - required) + Len(combo) + 1)
    newstr = combo & CStr(x)
    Call combine(required, max, newstr)
Next


End Function


Sub main()
Dim x As Long
Dim s As String

maxresult = 0
Call combine(3, 7, "")


s = ""
For x = 1 To maxresult
    s = s & result(x) & vbCrLf

Next
MsgBox (maxresult & " permutations" & vbCrLf & vbCrLf & s)

End Sub

lala · Feb 19, 2011

the_net_2.0 said:
why don't you give an example of a group of inputs and then the group of outputs that the code would otherwise give you? OK?

i won't be trying this for a while, because I've got appointments today.

you don't owe me anything)))))))) i'd appreciate whatever and whenever you can do

i'm going to make something in excel and attach in a few mins and thank you for even trying

lala · Feb 19, 2011

the_net_2.0 said:
why don't you give an example of a group of inputs and then the group of outputs that the code would otherwise give you? OK?

i won't be trying this for a while, because I've got appointments today.

here are the examples and while i was creating them Gemma posted some code that im about to study. Gemma, before i even try to undeerstand it, thank you for taking the time to explain what it does

lala · Feb 19, 2011

gemma-the-husky said:
...

Gemma, i now realize that i will never understand how this works, i forgot factorials back when i was taught them. so i'm just going to copy and paste. few questions though, i only have 9 fields at the max and considering that i'm going down to 3 combinations i'm only using 6 levels, is that right?

also, you said that if i use them too deeply it will crash the program, what do you mean by that? and thank you again, this is amazing!!

the_net_2.0 · Feb 19, 2011

lala said:
also, you said that if i use them too deeply it will crash the program, what do you mean by that?

programs run on memory, and memory is stacked by the computer system when it's used. If too much is used too quickly, the stack overflows, just like a glass overflows if too much water is poured into it. Thus, the program shuts down.

The same thing would be if a manager was managing too many people that he couldn't handle. Eventually he says "forget this, I'm not killing myself just to cater to others' needs". The same is true for a processor.

lala · Feb 19, 2011

in this case what would i have to do to crash it? too deeply meaning what?

get all combination of a set of numbers (1 Viewer)

Registered User.

Banned

Attachments

AWF VIP

Retired

Registered User.

Registered User.

Registered User.

Registered User.

Banned

Registered User.

Registered User.

Banned

Registered User.

Banned

Super Moderator

Registered User.

Registered User.

Attachments

Registered User.

Banned

Registered User.

Similar threads

Users who are viewing this thread