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
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