PDA

View Full Version : [SOLVED] Need help need VBA to build combinations from values from structured data



estatefinds
02-25-2016, 03:43 PM
My goal is to build combinations from values from specific areas of the chart and following a path of left to right and top to bottom until all combinations are found following this method.
i did provide and example to follow.
Please if you have questions please don't hesitate to ask and ill try to be more descriptive of the process.

i have been struggling with this for a while.

I have this data that changes but before I change the data I want to run a macro that would produce combinations based on certain criteria. so the data will look random but is set up in such a way it is organized in groups and those groups are labeled according to where they are found in the larger data. so the data is found on rows and these are placed in a column labeled row 1 the next row down in the large data is row 2 and placed in a column to the right of the column labeled row 1 so this would be called row 2 and so.
so now for the function of the VBA, the vba needs to look at the data in the column labeled row 1 and start at the top number. well say the number 1,in this list
1
2
9
24
33
this will be the first number to start building a combination in successive order meaning the number that is closest to it in the near by columns so for example the number 1 is in the column labeled row 1 and the top number listed is number 1. the second column labeled row 2 will have the numbers in a list
8
19
21
27
35
so the when the macro runs it will start at the first number 1 in the column labeled row 1, then it will look for four more numbers to make a combination of five numbers. so it will take one number from each column from left to right. So the number 1 will seek out the next number up from 1 which will be the number 8 not 19 because 19 is not the closest number to the number 1 , the closest number is 8 so this would be the number placed, so now we have two number of the five to make a combination now we continue to the right to the next columns labeled row 3,4,5,6,7,8,9,etc which ever column labeled row 3,4,5,6,7,8,9 has a number that is closest to the number 8. so the macro looks at the next row to see if a number is closest to 8
5
12
22
29
so the number wouldn't be 5 because it is below the number 8 and the combinations need to be built in successive order from smallest to largest. so the number that should be picked is the number 12.therfore, we have the three numbers to build a combination 1-8-12 and we need two more to make a five number combination.
so we look to the right again in the columns labeled row 4,5,6,7,8,9,10,11,12,13, etc. for the next number closest to the number 12 but above 12.
14
32
so in column labeled row 4 we pick the number 14 cause this number is closest to the number 12, so now we have 1-8-12-14.
then the vba looks in to the columns to the right in columns labeled row 5,6,7,8,9,10,11,12,13,14,15, etc.





18
34
so in the column labeled row 5 the number 18 will be picked to add to the combination as it is the closest number to the number 14, so now we have a five number combination 1-8-12-14-18.
so now here is where most people get stuck. the macro needs to keep running to find the all possible combinations that are left to find using the number left over from left to right. I will attach the example upon request. really need help with this, please!!!
also after you look a the CHART png you ll see that once a set is done it will do the same process butt this time it will for example will be 1-8-22-25-26 so where last time it took the first umber closets to 8 this time it skipped that one and went to the number 22 then continued to the closer number to 22 and so on.
also the first number that starts the combination for example the number 1 all the resulting combination will be placed in the column labeled row 1. because the number 1 is found in the column labeled row 1. now if the starting number is found in column labeled row 2 the resulting combinations will be placed in the column labeled row 2 and so on. Thank you






DC
DD
DE
DF
DG
DH
DI
DJ
DK
DL
DM
DN
DD
DP
DQ
DR
DS
DT
DU
DV
DW
DX
DY
DZ
EA
EB
EC
ED
EE
EF
EG
EH
EI
EJ
EK







































Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
Row 7
Row 8
Row 9
Row 10
Row 11
Row 12
Row 13
Row 14
Row 15
Row 16
Row 17
Row 18
Row 19
Row 20
Row 21
Row 22
Row 23
Row 24
Row 25
Row 26
Row 27
Row 28
Row 29
Row 30
Row 31
Row 32
Row 33
Row 34
Row 35







































A1
A2
A3
C4
C5
A6
C7

A9
A10
E11
E12
B13
D14
B15


A18



C22












E35


B1
B2
B3
E4
E5
B6
D7

C9




E14























C1
C2
C3


D6































D1
D2
D3


































E1
E2













































































































1
8
5
14
18
4
13

16
3
28
30
10
17
11


6



20












23


2
19
12
32
34
7
25

26




31























9
21
22


15































24
27
29


































33
35













































































































Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
Row 7
Row 8
Row 9
Row 10
Row 11
Row 12
Row 13
Row 14
Row 15
Row 16
Row 17
Row 18
Row 19
Row 20
Row 21
Row 22
Row 23
Row 24
Row 25
Row 26
Row 27
Row 28
Row 29
Row 30
Row 31
Row 32
Row 33
Row 34
Row 35


1-8-12-14-18
8-12-14-18-25
5-14-18-25-26
14-18-25-26-28
18-25-26-28-30
4-13-16-28-30
13-16-28-30-31

16-17-20-23-31
3-10-17-20-23


10-17-20-23-31
























1-8-12-14-34
etc
etc
etc
etc
etc
etc
etc
etc
3-10-11-20-23


10-11-20-23-31
























1-8-12-14-15
etc
etc
etc
etc
etc
etc
etc
etc
3-11-20-23-31


etc
























1-8-12-14-25
etc
etc
etc
etc
etc
etc
etc
etc
3-17-20-23-31


etc
























1-8-12-14-16
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-12-14-26
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-12-14-28
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-12-14-30
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-12-14-17
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-12-14-31
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-22-25-26
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-22-25-28
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-22-25-30
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-22-25-31
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-29-32-34
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-29-30-31
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-14-18-25
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-14-18-26
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-14-18-28
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-14-18-30
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-14-1831
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-14-18-20
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-14-18-23
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-18-25-26
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-18-25-28
etc
etc
etc
etc
etc
etc
etc
etc
etc


etc
























1-8-18-25-30








etc


etc
























1-8-18-25-31








etc



























1-8-15-25-26








etc



























1-8-15-25-28




































1-8-15-25-30




































1-8-15-25-31




































1-8-15-26-28




































1-8-15-26-30




































1-8-15-26-31




































1-8-15-28-30




































1-8-15-28-31




































1-8-15-30-31




































1-8-15-17-20




































1-8-15-17-23




































1-8-15-20-23




































1-8-13-16-28




































1-8-13-16-30




































1-8-13-16-17




































1-8-13-16-20




































1-8-13-16-23




































1-8-25-26-28




































1-8-25-26-28




































1-8-25-26-30




































1-8-25-26-31




































1-8-26-28-30




































1-8-26-28-31




































1-8-28-30-31




































1-8-29-32-34




































1-5-14-18-25




































1-5-14-18-26




































1-5-14-18-28




































1-5-14-18-30




































1-5-14-18-20




































1-5-14-18-23




































1-5-14-25-26




































1-12-14-18-25




































1-12-14-18-26




































1-12-14-18-28




































1-12-14-18-30




































1-12-14-18-31




































1-12-14-18-20




































1-12-14-18-23




































1-22-25-26-28




































1-22-25-26-30




































1-22-25-26-31




































1-22-26-28-30




































1-22-26-28-31




































1-22-28-30-31




































1-14-18-25-26




































1-14-18-25-28




































1-14-18-25-30




































1-14-18-25-31




































1-14-18-26-28




































1-14-18-26-30




































1-14-18-26-31




































1-18-25-26-28




































1-18-25-26-30




































1-18-25-26-31




































1-18-25-28-30




































1-18-25-28-31




































1-18-26-28-30




































1-18-26-28-31




































1-4-13-16-28




































1-4-13-16-30




































1-4-13-16-17




































1-4-13-16-20




































1-4-13-16-23




































1-4-13-16-31




































1-13-16-28-30




































1-13-16-28-31




































1-16-28-30-31




































1-16-17-20-23




































1-26-28-30-31




































1-3-28-30-31




































1-3-10-17-20




































1-3-10-17-23




































1-3-10-11-20




































1-3-10-11-23




































1-3-17-20-23




































1-3-10-17-31




































1-3-10-11-31




































1-10-17-20-23




































1-11-20-23-31




































2-8-12-14-18




































2-8-12-14-15




































2-8-12-14-25




































2-8-12-14-16




































2-8-12-14-28




































2-8-12-14-30




































2-8-12-14-17




































2-8-12-14-20




































2-8-12-14-23

snb
02-25-2016, 04:05 PM
Why don't you upload a sample file ?

What do you need this for ?

estatefinds
02-25-2016, 04:18 PM
here is the sample,
I am trying to analyze data that works with substrates of my glucose monitoring system
and how these combinations result based on their positions where the numbers are pulled from to make these combinations.
Thank you

estatefinds
02-25-2016, 05:32 PM
hello, let me know if you had a chance to review. Thank you

estatefinds
02-25-2016, 05:35 PM
also on the sample ignore the alpha numerical data as this was just used to organize the data.

SamT
02-25-2016, 11:02 PM
I reviewed the example.

You are making our job too hard.

Start the data in Column A, not in column DC.

Put raw data on one sheet and the desired result on a different sheet and DON'T USE "ETC!" you are the only person in the world who knows what it is supposed to mean in that table. Make the effort to create a good example of what is happening.

Us a third sheet to explain how the data gets from Raw to Result.

snb
02-26-2016, 03:05 AM
I can't detect any pattern/system in what you presented (I even fear you made some mistakes).

estatefinds
02-26-2016, 05:19 AM
ok so starting in the first number is 1 the macro will run so it looks to the right the first closest number found out out of the columns to the right but it will always build combinations meaning it will take the number 1 from the first column then it will look for the next column for a number that can be used to build a combination of five numbers, it as to follow the rule from left to right then it will eventually go from top to bottom until all combinations that can be made be listed. also the once the columns 1 is done it will start a search a column 2 it will start again with the top number. so what this is supposed to do is search the data that is presented and build combinations five number combinations taking a number from each column from left to right. the combination cant use the same two numbers from one column to build a combinations.
Yes I am starting data in DC so once this is done I can add the data in which this will be used.

ok sorry for using the ETC I am new at this. Ok so I put the ETC just to show the combinations keep going until all found for that column or should I say the column labeled Row 1-35.

ill write back to give a better example, thank you

estatefinds
02-26-2016, 07:53 AM
please review the additional files for my post Thank you

estatefinds
02-26-2016, 08:13 AM
so essentially it is stacking numbers; building a higher number upon the previous lower number from each column labeled row 1 to row 35 to build a five number combination.

so from left to right it will look at first column to start the process and go to another column to seek out the next highest number and this will continue to get a five number combination. so it takes the next highest number out of each subsequent column only if the number follows the smallest to greatest.


and the range of numbers used to that are found in the combinations are 1 to 35.

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28, 29,30,31,32,33,34,35

SamT
02-26-2016, 02:22 PM
I need to know that I truly understand. See if this is correct.

15483

estatefinds
02-26-2016, 03:04 PM
yes that is good! now the results that are placed on the row 9 should be placed under the column labeled row 1 since
the combination starts with the number 1 and this number 1 is found in the column labeled row 1.

estatefinds
02-26-2016, 03:08 PM
take look at this but you are getting there!!!

SamT
02-26-2016, 03:51 PM
I think that the Row Labels get in the way of understanding because they don't relate to any actual Row numbers on any Worksheet.

Especially in the example I used, which is transposed from your example, so they would have to be Column Labels in any case.

Once the code is working the results can be placed in either orientation and you can pad the Rows and Columns as desired.

estatefinds
02-26-2016, 04:00 PM
ok Great! Thank you! let me know when ready So I can test to be sure we get the desired result. Thank you very much!

estatefinds
02-28-2016, 07:28 AM
Hi, how are ya?
how is it coming along?
Sincerely,
Dennis

SamT
02-28-2016, 12:00 PM
It is Monday, I am just starting. It is a nice day and I have outdoor things that must be done. Patience my friend, you will get what you are paying for :D

estatefinds
02-28-2016, 12:10 PM
ok, no problem! Thank you very Much!!!

SamT
03-02-2016, 09:25 PM
I'm working on it. I just need some LSD to see the solution clearly. :D



@snb,

Would you please write the code to extract the "Only numbers not previously used", using my last attachment as an example, from the "Raw Data" from the OP's first attachment, and put it in A1 on a new sheet without empty rows?

The difference is that I want the data transposed from his original.

estatefinds
03-02-2016, 10:06 PM
this may help better understand where I get the numbers. Thank you!!!

snb
03-03-2016, 12:52 AM
Step 1:


Sub M_snb()
sn = Cells(9, 1).CurrentRegion

For j = 1 To UBound(sn)
For jj = 1 To UBound(sn, 2)
If InStr(c00, " " & sn(j, jj) & " ") <> 0 Then sn(j, jj) = "~"
c00 = c00 & " " & sn(j, jj) & " "
Next
Next

For j = 1 To UBound(sn)
sp = Filter(Application.Index(sn, j), "~", 0)
For jj = 1 To UBound(sn, 2)
sn(j, jj) = ""
If jj - 1 <= UBound(sp) Then sn(j, jj) = sp(jj - 1)
Next
If UBound(sp) > -1 Then c01 = c01 & " " & j
Next
sp = Application.Transpose(Split(Trim(c01)))

Cells(45, 7).Resize(UBound(sp), UBound(sn, 2)) = Application.Index(sn, sp, Array(1, 2, 3, 4, 5))
End Sub

estatefinds
03-07-2016, 07:05 AM
Hi, how are you doing?
Just checking in to see how it's coming along. 😃

snb
03-07-2016, 08:40 AM
Did you overlook the code in #21 ?

estatefinds
03-07-2016, 09:20 AM
Yes and got this results
A1 A2 A3 C4 C5 #N/A #N/A
B1 B2 B3 E4 E5 #N/A #N/A
C1 C2 C3 D6 #N/A #N/A
D1 D2 D3 #N/A #N/A
E1 E2 #N/A #N/A

SamT
03-07-2016, 09:21 AM
I hope snb has it, because I am having sleepless nights trying to imagine loop controls that don't require a brute force method.

Edit to add: Apparently not yet, but it looks like he's on the right track.

estatefinds
03-07-2016, 09:24 AM
this is what is showed

snb
03-07-2016, 09:32 AM
The code was meant for the file you posted in #20.

estatefinds
03-07-2016, 10:01 AM
please review what I wrote on the worksheet ti see if this helps paint a picture of what I am trying to accomplish. I just a VBA macro to build combinations from the data from left to right an when those combinations are made and found the macro will go downward in the data to the next number to build more combinations. following the rule left to right


once combinations are made the macro will go down to the next number and keep building from left to right and so on. so starting for example from the number 1 in column A the macro will look to the right from top to bottom for the next number above the number 1 for example 2-35.
for example

1-8-12-14-18


1-8-12-14-34


1-8-12-14-15


1-8-12-14-25


1-8-12-14-16


1-8-12-14-26
so the macro takes one number from each column from the left starting at the number 1 and taking a number from the right to build a five number combination. keeping in mind not to use two numbers from the same column; the combinations are built by one number from each column. this will continue until all possible combinations can be made following this rule.
then it will look at the next number down which is the number 2 in column A.

estatefinds
03-07-2016, 10:17 AM
so if it is being looked at this way then the combinations will be built top to bottom left to right.


1-8-12-14-18


1-8-12-14-34


1-8-12-14-15


1-8-12-14-25


1-8-12-14-16


1-8-12-14-26


1-8-12-14-28


1-8-12-14-30


1-8-12-14-17


1-8-12-14-31





Raw Data







1
2
9
24
33



8
19
21
27
35



5
12
22
29




14
32






18
34






4
7
15





13
25






16
26






3







28







30







10







17
31






11







6







20







23

estatefinds
03-07-2016, 10:22 AM
it worked thank you. but let me know if you can help me with what i had attached. thank you

SamT
03-07-2016, 02:26 PM
it worked thank you.
Good. I'm done.

estatefinds
03-07-2016, 03:47 PM
VBA MACRO
Hello, this may help alot. please take a look and if you have questions let me know thank you!

estatefinds
03-07-2016, 03:48 PM
VBA MACRO
Hello, this may help alot. please take a look and if you have questions let me know thank you!

snb
03-08-2016, 01:13 AM
I'm glad somebody gave you some code.