# Loop thru every combination of variables, calculate and paste

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

## Files

• 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?

• 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?

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

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

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.

• 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

• 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

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!