Add loop values to Array and assign SUM of Top 3 Values to variable

S

SidKol

New Member
Joined
Oct 14, 2015
Messages
47
Hi all, got a challenge :)

I have a loop running through my code.
I will not post the entire code, but it goes through millions of combinations, created from 15 columns filled with prices.
Each combination consists of 15 values (one value from each column)

Within these columns there are groups.
For example the values from the first two columns can be considered group1,
and the values used from column 3 to 7 can be considered group2

First I had to find the largest value in each possible combination within group 2 - which was doable for me like this:

Code: 
 Largest1PriceGroup1 = Cells(a, 49).Value
                        If Cells(a, 49).Value < Cells(b, 50) Then
                            LargestPriceGroup1= Cells(b, 50).Value
                        End If

However, after this I need to SUM the largest 3 of 5 values in group 2

Price3 = Cells(c, 51).Value
Price4 = Cells(d, 52).Value
Price5 = Cells(e, 53).Value
Price6 = Cells(f, 54).Value
Price7 = Cells(g, 55).Value


So if Price 3,4, and 5 are all € 5
And Price 6 and 7 are both € 1
Then "Largest3PriceGroup2" should be 15 within this iterination

For this I can write a very large code (in a way similar to what i did with group 1), but I am sure that there has to be a way more efficient method for this.

What I think needs to be done is to create an Array that contains the values of price 3 to 5 and then efficiently assigns the SUM of the top 3 values to "Largest3PriceGroup2"

Usually Google and your forum are my best friends for this, but I still haven't been able to figure this out.
Therefore I hope one of you is willing to help me out on my quest :)

All help greatly appreciated ofcourse!
 
Last edited:
Sort by date Sort by votes
No problem, very glad that you are trying to help so much and I am more than happy to explain more.

1. Why in the second combination (loop) the largest value for Group1 is still 5? Why not 7,99 (A5)?

The second combination can be found in the screenshot in row 21.
Price1 = 5
Price2 = 4,6
Hence the largest value in Group1 is 5

2. When there are less than 3 values in Group2 like in C7:G7 what the code should do?

There are always 5 values in group2.
The code combines every single value from each column with one single value from every other column and ignores empty values.

Maybe it helps if I completely simplify the combination generation:

When applying the same to 3 columns with only options A and B

A A A
B

This would result in a total of 2 possible combinations

A A A
B A A


Hope this helps to understand.
 
Last edited:
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Checked my initial post and found something incorrect that may have caused some of the confusion

“What I think needs to be done is to create an Array that contains the values of price 3 to 5”

Should be 3 to 7
 
Last edited:
Upvote 0
I definitively don't understand what you are trying to do. Maybe someone else can help.

M.
 
Upvote 0
Hi Marcelo, Thank you very much anyway for taking the time to try to help.
Really appreciate it a lot.


I don't know if you are still interested, but maybe it can help if we try setting up a similar code together from scratch.
If not hopefully somebody else is willing to help out.


Input:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Price 1[/TD]
[TD]Price 2[/TD]
[TD]Price 3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Price 1[/TD]
[TD]Price 2[/TD]
[TD]Price 3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Output should be all possible combinations that contain:
one value from column A
one value from column B
one value from column C.

All generated combinations need to be displayed under the input data

So desired outcome here is:


[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Price 1[/TD]
[TD]Price 2[/TD]
[TD]Price 3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Price 1[/TD]
[TD]Price 2[/TD]
[TD]Price 3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

As these are all the 6 possible combinations that can be generated from the input values.


This I have been able to do myself (see code underneath).
However I am really curious to know how this could be set up as efficiënt as possible.


Code: 
Sub generatecombinations()


           'start from row 7 with results
            X = 7
    
            'clear previous result data
            With Sheets("Sheet1")
            .Rows(7 & ":" & .Rows.count).Delete
            End With
            
            
            'Count values in column A, B and c
            AmountValuesA = WorksheetFunction.CountA(Range("A2:A4"))
            AmountValuesB = WorksheetFunction.CountA(Range("B2:B4"))
            AmountValuesC = WorksheetFunction.CountA(Range("C2:C4"))
            


            'Loop through all values in price columns
            For a = 2 To 1 + AmountValuesA
            For b = 2 To 1 + AmountValuesB
            For c = 2 To 1 + AmountValuesC
            


            'Create a row with results
                        Cells(X, 1) = Cells(a, 1)
                        Cells(X, 2) = Cells(b, 2)
                        Cells(X, 3) = Cells(c, 3)
                        
            'Set the next row for results
                        X = X + 1
            
            'Continue loop
            Next c
            Next b
            Next a
            
End Sub


Hopefully you can help me with a more efficient code (as the real document contains millions of combinations to go through)

As soon as this is set up, we can add the final touch.
Which is to only get those combinations that have a sum of the highest two values that is at least 4

So eventually the desired result should be:


[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Price 1[/TD]
[TD]Price 2[/TD]
[TD]Price 3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Price 1[/TD]
[TD]Price 2[/TD]
[TD]Price 3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Because this is the only of the 6 combinations of which the SUM of the highest 2 values is at least 4
 
Last edited:
Upvote 0
To display only the combinations whose sum of the two highest is equal or greater than 4, maybe something like this

Code: 
Sub GenCombinsV2()
    Dim X As Long, a As Long, b As Long, c As Long
    Dim AmountValuesA As Long, AmountValuesB As Long, AmountValuesC As Long
    Dim vResult As Variant, Sum2Highest As Double, Max2 As Double
    
    With Sheets("Sheet1")
        'clear previous result data
        .Rows(7 & ":" & .Rows.Count).Delete
     
        'Count values in column A, B and c
        AmountValuesA = WorksheetFunction.CountA(Range("A2:A4"))
        AmountValuesB = WorksheetFunction.CountA(Range("B2:B4"))
        AmountValuesC = WorksheetFunction.CountA(Range("C2:C4"))
        
        'Create an array to store combinations sum 2 highest > =4
        vResult = .Range("A7").Resize(AmountValuesA * AmountValuesB * AmountValuesC, 3)
    
        'Loop through all values in price columns
        For a = 2 To 1 + AmountValuesA
            For b = 2 To 1 + AmountValuesB
                For c = 2 To 1 + AmountValuesC
                    'Calc sum of 2 highest
                    Sum2Highest = Application.SumProduct( _
                    Application.Large(Array(.Cells(a, 1), .Cells(b, 2), .Cells(c, 3)), Array(1, 2)))
                                                       
                    If Sum2Highest >= 4 Then
                        X = X + 1
                        vResult(X, 1) = .Cells(a, 1)
                        vResult(X, 2) = .Cells(b, 2)
                        vResult(X, 3) = .Cells(c, 3)
                    End If
                Next c
           Next b
        Next a
        .Range("A7").Resize(AmountValuesA * AmountValuesB * AmountValuesC, 3) = vResult
    End With
End Sub

Hope this helps

M.
 
Upvote 0
Hi Marcelo,

Thank you so much again. Getting very close to what is needed.

Your code is exactly what is needed IF the full code would only generate a small number of combinations.
However, the real code generates millions of possible combinations, causing a problem regarding size of the array.

'Create an array to store combinations sum 2 highest > =4
vResult = .Range("A7").Resize(AmountValuesA * AmountValuesB * AmountValuesC, 3)
Click to expand...


Causing the array to give an error message when becoming too big:
Run-time error '1004': Application-defined or object-defined error.

Is this something you are able to solve?
 
Upvote 0
Excel has some limitations as the number of rows in a worksheet - 1048576 rows.
Maybe Excel is not the right software to solve your problem.

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
select value two columns to the left of variable in an array
Replies
1
Views
271
myall_blues
myall_blues
B
  • Question Question
Vba code to loop through a column and return values to label controls
Replies
3
Views
323
Trebor76
T
U
  • Solved
Count Number of Times Values appear in a Column and Rank top 3
Replies
5
Views
195
Fluff
Fluff
S
  • Question Question
A tricky array sum question into a single cell...
Replies
4
Views
310
etaf
etaf
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
410
rahildhody
R

Forum statistics

Threads
1,223,629
Messages
6,173,434
Members
452,514
Latest member
cjkelly15

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top