How can you populate calculation tree easily?

[adamis]

I need to be able to fill in all of the possible calculations from up to 5 columns of information. For example, I have 3 columns with an unknown number of cells populated in each column (in the example below I just limited it to three rows).

1 a !
2 b @
3 c #


I need the macro to produce the following:



1 a !
1 a @
1 a #
1 b !
1 b @
1 b #
1 c !
1 c @
1 c #
2 a !
2 a @
2 a #
2 b !
2 b @
2 b #
2 c !
2 c @
2 c #
3 a !
3 a @
3 a #
3 b !
3 b @
3 b #
3 c !
3 c @
3 c #


I have 5 columns of information that needs to be branched like this so it would take quite a while to do it by hand.

Any suggestions?

 

[Greg Truby]

Can you show us the code you have written so far...

 

[adamis]

I'm still working on it (I'm not very proficient with VBA). I was hoping that there was something like this that has already come up and answered. I'll post some code when if I manage to get something headed in the right direction.

 

[GrayMPA]

Use loops in VBA. use a count function to find the number of rows in each column. To do this with 2 columns:

Dim alastrow As Integer
Dim blastrow As Integer
Dim x As Integer
Dim a As Integer
Dim b As Integer

alastrow = Range("a65536").End(xlUp).Row
blastrow = Range("b65536").End(xlUp).Row

x = 1

For a = 1 To alastrow
For b = 1 To blastrow

Cells(x, 4) = "" & Cells(a, 1) & Cells(b, 2) & ""
x = x + 1
Next b
Next a



End Sub

 

[Greg Truby]

I'm sure it has. But this looks a lot more like a fundamental homework assignment to teach one how to apply nested loops than a work-related project; hence my reticence to just toss out the solution.

 

[Greg Truby]

If this is not a homework assignment then the following solution should work for you. Note that the order of the columns is inverted since it makes extending the formula much simpler. This is an adaptation of a solution I describe here.

book2
	A	B	C	D
1	Col A	Col B	Col C
2	!	a	1
3	@	a	1
4	#	a	1
5	!	b	1
6	@	b	1
7	#	b	1
8	!	c	1
9	@	c	1
10	#	c	1
11	!	a	2
12	@	a	2
13	#	a	2
14	!	b	2
15	@	b	2
16	#	b	2
17	!	c	2
18	@	c	2
19	#	c	2
20	!	a	3
21	@	a	3
22	#	a	3
23	!	b	3
24	@	b	3
25	#	b	3
26	!	c	3
27	@	c	3
28	#	c	3
Sheet1 (2)

The core formula is:<ul>[*]=CHOOSE(MOD(ROUNDUP((ROW()-ROW(A$1))/n^(COLUMN()-1),0)+(n-1),n)+1,"item 1","item 2","item n")[/list]You put the list of items to be iterated through in the CHOOSE() function. You alter the n's and the +(n-1)in the formula to be the number of items in your list. For example if you had four items the formula would be:<ul>[*]=CHOOSE(MOD(ROUNDUP((ROW()-ROW(A$1))/4^(COLUMN()-1),0)+3,4)+1,"a","b","c","d")[/list]
HTH

 

[adamis]

Okay... Give a man a fish and he will come back the next day. Teach a man to fish and he will get his own fish. Got it...

Here's what I have so far...

Sub Populate()
Total = Cells(1, 5).Value * Cells(1, 6).Value

For i = 1 To Total

Cells(i, 7).Value = Cells(1, 1)

Next i

For i = 1 To Total

Count = i

For x = 1 To Cells(1, 5).Value

Cells(Count, 8).Value = Cells(x, 1)

Count = Count + 1

Next x

i = i + Cells(1, 5).Value - 1

Next

End Sub

 

[Richard Schollar]

If you wanted to do it mega-quick with a large number of items and without the use of VBA then you could write a SQL statement and go thru MSQuery to generate the output (it's simply a cartesian product). Or do it in Access or any other database program (especially if you have 250 or so records in each column as you'll exceed Excel's row capacity).

Richard

 

[Greg Truby]

adamis -- see my post for a formula-based solution. If you really require a VBA-based solution, try applying what you see in greympa's post, i.e. nested loops, which is really what this boils down to and post back if you get stuck.<hr />
Richard,

OK, you have my attention. Can you flesh that out a bit? I don't play with MSQuery at all -- probably because I've never learned much about it.

 

[adamis]

Here's my solution

Ok, Here's my solution, it's based on columns A-E containing the data that needs to be processed. Column A is for Level A, Column B is for Level B and so on.

Columns F-J contain the count total for each column respectively. IE Column F is counting the number of rows that contain data in column A, Column G is counting the number of rows that contain data in column B etc...

The output of the script is set to put the information in columns K-O.

So far the script is working for what I need, I'm sure there could be plenty of tweaks and bugs though...






<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Populate()

    Total = Cells(1, 6).Value * Cells(1, 7).Value * Cells(1, 8).Value * Cells(1, 9).Value * Cells(1, 10).Value

<SPAN style="color:#007F00">''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Column 1</SPAN>
    Column1 = Cells(1, 7).Value * Cells(1, 8).Value * Cells(1, 9).Value * Cells(1, 10).Value
        
    x = 1
    
    <SPAN style="color:#00007F">For</SPAN> u = 1 <SPAN style="color:#00007F">To</SPAN> Cells(1, 6).Value
            
        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Column1
    
            Cells(x, 11).Value = Cells(u, 1)
            
            x = x + 1
        
        <SPAN style="color:#00007F">Next</SPAN> i
                
    <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#007F00">''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Column 2</SPAN>
    
Column2 = Cells(1, 8).Value * Cells(1, 9).Value * Cells(1, 10).Value
    
    x = 1
        
<SPAN style="color:#00007F">For</SPAN> y = 1 <SPAN style="color:#00007F">To</SPAN> Cells(1, 6).Value
    
    <SPAN style="color:#00007F">For</SPAN> u = 1 <SPAN style="color:#00007F">To</SPAN> Cells(1, 7).Value
            
        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Column2
    
            Cells(x, 12).Value = Cells(u, 2)
            
            x = x + 1
        
        <SPAN style="color:#00007F">Next</SPAN> i
                
    <SPAN style="color:#00007F">Next</SPAN>
    
<SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#007F00">'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Column 3</SPAN>


Column3 = Cells(1, 9).Value * Cells(1, 10).Value
    
    x = 1
        
<SPAN style="color:#00007F">For</SPAN> y = 1 <SPAN style="color:#00007F">To</SPAN> Cells(1, 6).Value * Cells(1, 7).Value
    
    <SPAN style="color:#00007F">For</SPAN> u = 1 <SPAN style="color:#00007F">To</SPAN> Cells(1, 8).Value
            
        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Column3
    
            Cells(x, 13).Value = Cells(u, 3)
            
            x = x + 1
        
        <SPAN style="color:#00007F">Next</SPAN> i
                
    <SPAN style="color:#00007F">Next</SPAN>
    
<SPAN style="color:#00007F">Next</SPAN>

<SPAN style="color:#007F00">'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Column 4</SPAN>

Column4 = Cells(1, 10).Value
    
    x = 1
        
<SPAN style="color:#00007F">For</SPAN> y = 1 <SPAN style="color:#00007F">To</SPAN> Cells(1, 6).Value * Cells(1, 7).Value * Cells(1, 8).Value
    
    <SPAN style="color:#00007F">For</SPAN> u = 1 <SPAN style="color:#00007F">To</SPAN> Cells(1, 9).Value
            
        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Column4
    
            Cells(x, 14).Value = Cells(u, 4)
            
            x = x + 1
        
        <SPAN style="color:#00007F">Next</SPAN> i
                
    <SPAN style="color:#00007F">Next</SPAN>
    
<SPAN style="color:#00007F">Next</SPAN>

<SPAN style="color:#007F00">'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Column 5</SPAN>

Column5 = Cells(1, 11).Value
    
    x = 1
        
<SPAN style="color:#00007F">For</SPAN> y = 1 <SPAN style="color:#00007F">To</SPAN> Cells(1, 6).Value * Cells(1, 7).Value * Cells(1, 8).Value * Cells(1, 9).Value
    
    <SPAN style="color:#00007F">For</SPAN> u = 1 <SPAN style="color:#00007F">To</SPAN> Cells(1, 10).Value
            
        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Column5
    
            Cells(x, 15).Value = Cells(u, 5)
            
            x = x + 1
        
        <SPAN style="color:#00007F">Next</SPAN> i
                
    <SPAN style="color:#00007F">Next</SPAN>
    
<SPAN style="color:#00007F">Next</SPAN>


<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>