Loop thru every combination of variables, calculate and paste

rinconpaul · Jul 11th 2015

I have 4 cols C, V, S, N. Each column has two values, 1 or 2. The combination of those 4 column values makes 16 distinct records i.e. 1-1-1-1, 1-1-1-2, 1-1-2-1....2-2-2-2. Each record has a 5th value on the end, a "W" or "L", making 32 combinations. The list of data is added to each day. As required I need a vba code to trigger the auto filter to find all records for each combination and pair the "W" &"L" records side by side. i.e. say 1-1-2-2-W alongside 1-1-2-2-L. It then performs 3 calculations on the two subsets, and the data dragged with it from the data sheet, namely "P" and "$".

Once the 3 calculations are performed it will paste these to a new worksheet, then loop back to find the next consecutive combination, calculate, paste after the last paste and repeat till the cycle is complete. As the data is added to each day it will include the range extension each time it is run. The real life scenario for this comprises 8 variables for each column (1...8) and the number of rows is in the 200,000 plus category. That makes for 8000plus combinations if you include the "W" & "L" variants. You might like to incorporate the 1 thru 8 variables per column in your code or just use the 1 thru 2 as per the attached example, and I'll modify.

Cheers for reading this post.

GCExcel · Jul 12th 2015

Re: Loop thru every combination of variables, calculate and paste

Cross posted here: http://www.excelforum.com/exce…-calculate-and-paste.html

rinconpaul · Jul 12th 2015

Re: Loop thru every combination of variables, calculate and paste

Confused here, has GCExcel actually posted a reply?. Can see the blue shirt logo, but nothing else?

**cytop** · Jul 12th 2015

Re: Loop thru every combination of variables, calculate and paste

The actual text posted is "Cross posted here: http://www.excelforum.com/excel-prog...and-paste.html"

To extend that:

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

And if your're still reading, please read this.
...

rinconpaul · Jul 12th 2015

Re: Loop thru every combination of variables, calculate and paste

Well, if you call yourself two different websites, is it any wonder? I never knew that Ozgrid was Excel Help Forum, as I've got different passwords. VERY CONFUSING!! Is that my fault....methinks not?

**cytop** · Jul 12th 2015

Re: Loop thru every combination of variables, calculate and paste

Quote

.methinks not?

Me thinks so - You posted this issue on ExcelForum. ExcelForum & OzGrid are NOT the same...

Did you read the link I posted? If not then try this one - you call 2 of your best mates and ask both of them for a lift somewhere. Both agree.

You wait, eventually one of them gets there first so you bugger off with him without saying anything to the 2nd... How long you going to have friends?

Someone else used that analogy in a post the other day - I thought it described the issue perfectly.

**mikerickson** · Jul 12th 2015

Re: Loop thru every combination of variables, calculate and paste

rinconpaul,

Welcome.

"Double posting" is posting the same question on the same site.
"Cross posting" is posting the same question on different sites.

Most sites, including this one, forbid Double Posting.
The rules for Cross Posting are different, it is allowed, if the cross post is linked to by the asker. This link explains why these sites have that rule. http://www.excelguru.ca/content.php?184

Is it your "fault"? Not the first time, but now that you know.....

As to your question, what are the three calculations that are preformed on the filtered data? There is a good chance that those can be performed by SUMIFS or COUNTIFS or similar functions, omitting the need for filtering.

rinconpaul · Jul 12th 2015

Re: Loop thru every combination of variables, calculate and paste

Yes you're right, these two will provide the answers for each individual combination:
=SUMPRODUCT((H2:H2000="1-1-2-2-W")*(G2:G2000))-SUMPRODUCT((H2:H2000="1-1-2-2-L")*(G2:G2000))
=SUMPRODUCT((H2:H2000="1-1-2-2-L")*(B2:B2000>MAX((H2:H2000="1-1-2-2-W")*(B2:B2000)))*(G2:G2000))
The only thing needed now is a loop to work out & insert all the possible combinations of the array into those two formula? Don't forget in the real life situation there is 8x8x8x8 (4096) possible combinations. Can you suggest a code to implement that?
Cheers

**mikerickson** · Jul 13th 2015

Re: Loop thru every combination of variables, calculate and paste

You could use this UDF. The demo shows how to loop through all your combinations

Code

Sub demo()
    Dim myString As String
    Dim Overflow As Boolean
    myString = "1-1-1-1-L"
    
    Do
        Rem do something
        MsgBox myString
        myString = NextString(myString, Overflow)
    Loop Until Overflow
End Sub


Function NextString(strCurrent As String, Optional ByRef Overflow As Boolean) As String
    strCurrent = Replace(Replace(Replace(Replace(Replace(strCurrent, "-", vbNullString), "W", 2), "L", 1), 1, "0"), 2, 1)
   
    If strCurrent = "11111" Then
        strCurrent = "00000"
    Else
        strCurrent = WorksheetFunction.Dec2Bin(WorksheetFunction.Bin2Dec(strCurrent) + 1, 5)
    End If
    Overflow = (strCurrent = "00000")
    
    strCurrent = Replace(strCurrent, "1", "2")
    strCurrent = Replace(strCurrent, "0", 1)
    
    strCurrent = WorksheetFunction.Replace(strCurrent, 5, 0, "-")
    strCurrent = WorksheetFunction.Replace(strCurrent, 4, 0, "-")
    strCurrent = WorksheetFunction.Replace(strCurrent, 3, 0, "-")
    strCurrent = WorksheetFunction.Replace(strCurrent, 2, 0, "-")
   
    If Right(strCurrent, 1) = "1" Then
        NextString = Left(strCurrent, 8) & "L"
    Else
        NextString = Left(strCurrent, 8) & "W"
    End If
End Function

Display More

Loop thru every combination of variables, calculate and paste

Participate now!

Share