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

3-10-11-20-23

10-11-20-23-31

1-8-12-14-15

etc

3-11-20-23-31

etc

1-8-12-14-25

etc

3-17-20-23-31

etc

1-8-12-14-16

etc

1-8-12-14-26

etc

1-8-12-14-28

etc

1-8-12-14-30

etc

1-8-12-14-17

etc

1-8-12-14-31

etc

1-8-22-25-26

etc

1-8-22-25-28

etc

1-8-22-25-30

etc

1-8-22-25-31

etc

1-8-29-32-34

etc

1-8-29-30-31

etc

1-8-14-18-25

etc

1-8-14-18-26

etc

1-8-14-18-28

etc

1-8-14-18-30

etc

1-8-14-1831

etc

1-8-14-18-20

etc

1-8-14-18-23

etc

1-8-18-25-26

etc

1-8-18-25-28

etc

1-8-18-25-30

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

VBAX_SamT_better example.xlsx

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

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

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

Thread: Need help need VBA to build combinations from values from structured data

Thread Tools

Display

Need help need VBA to build combinations from values from structured data

additaional files

VBAX_Sam_T_better

Posting Permissions