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

[SidKol]

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:

 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!

 

[Marcelo Branco]

See if this helps

Say Group2 is in A2:E2

	A​	B​	C​	D​	E​
1​
2​	10​	20​	30​	20​	8​

Try this code

Sub aTest()
    Dim Group2 As Range, Largest3PriceGroup2 As Double
    
    Set Group2 = Range("A2:E2")
    Largest3PriceGroup2 = Evaluate("=Sum(Large(" & Group2.Address & ",{1,2,3}))")
    MsgBox Largest3PriceGroup2 'displays 70 (30+20+20)
End Sub

M.

 

[SidKol]

Hi Marcelo, thank you very much for your reply. I really appreciate it, but the range fixed to A2:E2 will not work unfortunately.

The code follows a loop, so in one iterination the value from A2 will be taken and in the next it will be the value from A3.

Therefore I need to add the values of c, 51 to g, 55 to an array.

(c, d, e, f, g) can loop from 1 to x

 

[Marcelo Branco]

Thie post above is just an example with a fixed range.
Can you set the proper range within the loop? For example:

Dim Rng As Range
For i = 1 To lastRow
    Set Rng = Range("A" & i).Resize(, 5)
    Largest3PriceGroup2(i) = Evaluate("=Sum(Large(" & Rng.Address & ",{1,2,3}))")
Next i

Of course the values should be stored in an array. Something like
Largest3PriceGroup2(i)

M.

 

[SidKol]

Hi Marcelo,

Thanks so much again. Maybe my (amateurish, selfmade) loop (that yet i am very proud of ) is not set up correctly for what is necessary
I have tried to implement your Range with Resize, but can't get it to work.

So I have extracted a part of the code that fully covers my question + a screenshot.

Sub testpriceresults()


            
            'start from row 20 with results
            X = 20
                
    
            'clear previous result data
            With Sheets("Sheet2")
            .Rows(X & ":" & .Rows.Count).Delete
            End With
            


            'Loop through all values in price columns
            'Values per column indicated in first row of columns with formula =14-COUNTIF(A4:A17;"") etc)
            For a = 4 To 4 + Cells(1, 1).Value - 1
            For b = 4 To 4 + Cells(1, 2).Value - 1
            For c = 4 To 4 + Cells(1, 3).Value - 1
            For d = 4 To 4 + Cells(1, 4).Value - 1
            For e = 4 To 4 + Cells(1, 5).Value - 1
            For f = 4 To 4 + Cells(1, 6).Value - 1
            For g = 4 To 4 + Cells(1, 7).Value - 1
            
            
            'Find most expensive value in first two columns:
            Largest1PriceGroup1 = Cells(a, 1).Value
               If Cells(a, 1).Value < Cells(b, 2) Then
               Largest1PriceGroup1 = Cells(b, 2).Value
               End If
            
            
            
            'Create a row with results
                        Cells(X, 1) = Cells(a, 1)
                        Cells(X, 2) = Cells(b, 2)
                        Cells(X, 3) = Cells(c, 3)
                        Cells(X, 4) = Cells(d, 4)
                        Cells(X, 5) = Cells(e, 5)
                        Cells(X, 6) = Cells(f, 6)
                        Cells(X, 7) = Cells(g, 7)
                        
                        
            'add largest price (Sums)
                        Cells(X, 9) = Largest1PriceGroup1
                        
            
            'Set the next row for results
                        X = X + 1
            
            
            'Continue loop
            
            Next g
            Next f
            Next e
            Next d
            Next c
            Next b
            Next a
End Sub

So this code loops through all combinations and gives me the highest price of group 2.
Yet I am still unable to implement the SUM of 3 highest prices in group2.

Hopefully you can help me to understand how to implement a solution, to make this code as efficient as possible.

 

[Marcelo Branco]

In your example (picture) above what are the ranges that correspond to Group1 and Group2?

M.

 

[SidKol]

Range of group 1:
Cells(a, 1)
Cells(b, 2)

Where a and b loop from 4 to 5

Range of group 2:
Cells(c, 3)
Cells(d, 4)
Cells(e, 5)
Cells(f, 6)
Cells(g, 6)

Where c loops from 4 to 7,
d, e and g loop from 4 to 6
And f from 4 to 5

 

[Marcelo Branco]

Question:
Are these the expected results?
Sum of 3 largest values of Group1
=7,99+5+4,6 = 17,59
Sum of 3 largest values of Group2
=7,2+7,2+7,2 = 21,6

M.

 

[SidKol]

No, it should only take the (one) largest value of group 1 and the sum of the largest 3 values of group 2 for EVERY loop iteration

So the code runs.. and after the first iteration the generated combination is put in cells A20 to G20

The largest value for group 1 within this combination is 5. This value is written to I20

The sum of the 3 largest of group 2 is 4,9 + 4,5 + 5,5 = 14,9
This value should be written in J20


After this the code runs for the second possible combination, this time the result is put in cells A21 to G21

The largest value for group 1 is still 5
This value is written to I21

The sum of 3 largest in group 2 is now 4,5 + 4,9 + 4,5 = 13,9
This value should be written to J21

And so on, until the whole loop is finished

 

[Marcelo Branco]

Sorry, i don't understand what you are trying to do. Confused
1. Why in the second combination (loop) the largest value for Group1 is still 5? Why not 7,99 (A5)?
2. When there are less than 3 values in Group2 like in C7:G7 what the code should do?

M.