Loop autofilter through multiple combinations, calculate and paste

[RINCONPAUL]

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.

[snb]

=SUMPRODUCT((H2:H2000="1-1-2-2-W")*(G2:G2000))-SUMPRODUCT((H2:H2000="1-1-2-2-L")*(G2:G2000))
resulting in: -4091,05

array formula:

=SUMPRODUCT((H2:H2000="1-1-2-2-L")*(B2:B2000>MAX((H2:H2000="1-1-2-2-W")*(B2:B2000)))*(G2:G2000))

resulting in: 2193,706

[SamT]

I take it that the Combination column won't be there.

I don't suppose that the data originates in a *.csv file?

What if a W combo doesn't have a matching L combo or vice versa? Your sample has that situation for 1111W.


For a = 1 to 2
For c = 1 to 2
For d = 1 to 2
For e = 1 to 2
X = Filter(Filter(Filter(Filter( Array, a), c), d), e)
Y = Filter( X, "W")
Z = Filter(X, "L")
Computations and Storage(Y, Z)
Next
Next
Next
Next

[RINCONPAUL]

=SUMPRODUCT((H2:H2000="1-1-2-2-W")*(G2:G2000))-SUMPRODUCT((H2:H2000="1-1-2-2-L")*(G2:G2000))
resulting in: -4091,05

array formula:

=SUMPRODUCT((H2:H2000="1-1-2-2-L")*(B2:B2000>MAX((H2:H2000="1-1-2-2-W")*(B2:B2000)))*(G2:G2000))

resulting in: 2193,706









Cheers for that VBAX Guru, you've taken the trouble to actually look at the question in detail, very impressive. Now all I need is a loop to insert every possible combination into the two formula. In the real world there will be 8x8x8x8 combinations (4096). Any suggestions?

[RINCONPAUL]

I take it that the Combination column won't be there.

I don't suppose that the data originates in a *.csv file?

What if a W combo doesn't have a matching L combo or vice versa? Your sample has that situation for 1111W.


For a = 1 to 2
For c = 1 to 2
For d = 1 to 2
For e = 1 to 2
X = Filter(Filter(Filter(Filter( Array, a), c), d), e)
Y = Filter( X, "W")
Z = Filter(X, "L")
Computations and Storage(Y, Z)
Next
Next
Next
Next

No SamT it doesn't originate from a csv file. Yes it is possible that some combinations would be without a 'W' or 'L'. Not sure what your code was trying to achieve, I just got a syntax error on "X= Filter..." & "Computations..."

[snb]

Sub M_snb()
sn = [index(dec2bin(row(1:256)-1,8),)]
ReDim sp(UBound(sn), 2)

For j = 1 To UBound(sn)
sp(j - 1, 0) = Replace(Replace(Replace(StrConv(sn(j, 1), 64), "1", "2"), "0", "1"), Chr(0), "-")
sp(j - 1, 1) = Evaluate("SUMPRODUCT((H2:H2000=""" & sp(j - 1, 0) & "W"")*(G2:G2000))-SUMPRODUCT((H2:H2000=""" & sp(j - 1, 0) & "L"")*(G2:G2000))")
sp(j - 1, 2) = Evaluate("=SUMPRODUCT((H2:H2000=""" & sp(j - 1, 0) & "L"")*(B2:B2000>MAX((H2:H2000=""" & sp(j - 1, 0) & "W"")*(B2:B2000)))*(G2:G2000))")
Next

Cells(1, 20).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
End Sub

[SamT]

Sorry, I wasn't clear enough. That "code" is just a programmers note of an algorithm the would be applicable to csv files. Please ignore.

Beside, it looks like snb has the solution for you.

[RINCONPAUL]

Hey, looks promising snb? The code, however spat out a five variable matrix, not a four variable? i.e. 1-1-2-1-2- and all the computations resulted in zeros. Shouldn't it be 1-1-2-1-.....-->

Nearly there, I think. Good work.

[RINCONPAUL]

Sorry, I wasn't clear enough. That "code" is just a programmers note of an algorithm the would be applicable to csv files. Please ignore.

Beside, it looks like snb has the solution for you.

No need to apologise, thankyou so much for taking the time to post.

[RINCONPAUL]

Whoops, sorry snb, the column wasn't wide enough and I could only see 5 numbers, you've done an 8 number matrix. I'll try that on the large data set. Fingers crossed.

[RINCONPAUL]

snb, I've attached a real life file. This is where going from a simple demo spreadsheet to a reality one, things get lost in the translation. I was remiss in my original posts that when I said that variables could go up to 8, that's not correct. I should have qualified it and said that col header C goes to 8 but col headers V,S,N could go as high as 20? I've modified the ranges in your code but it looks like it'd need another tweak to cater for the increase in variables. Much appreciate it if you could take the time to look at it?

Many thanks

[snb]

I will, tomorrow.

[RINCONPAUL]

Thanks champ :)

[RINCONPAUL]

On reflection, I've actually found a non vba way round this which expands the variables without the need to generate an array of combinations to loop through, many of which will come up as blanks (no qualifying combination). This method makes use of the existing combinations only:
Data set as per snb.xlsm.
Remove any error rows
Sort Combination col~ large no. --->small no.
In col 'I' paste formula: =SUMPRODUCT((H$2:H$20000=(J2&"-W"))*(G$2:G$20000))+SUMPRODUCT((H$2:H$20000=(J2&"-L"))*(G$2:G$20000))
In col 'J' paste formula: =A2&"-"&C2&"-"&D2&"-"&E2

For the other:
In col 'K' paste formula: =SUMPRODUCT((H$2:H$20000=J2&"-L")*(B$2:B$20000>MAX((H$2:H$20000=J2&"-W")*(B$2:B$20000)))*(G$2:G$20000))





In col 'I' you'll end up with negative and positive values. Filter out negative values and duplicates. In col 'K' you'll end up with positive numbers or zeros. Filter out zeros. This leaves you with profitable combinations. I can record a macro to do this.

From my point of view, case is closed now, and thanks so much to snb for your awesome assistance.

[snb]

It might be practical to

- sort column H first
- filter only unique combinations into column J


Sub M_snb()
Sheet1.Cells(1).CurrentRegion.Sort Sheet1.Cells(1, 8), , , , , , , 1
Sheet1.Cells(1).CurrentRegion.Columns(8).AdvancedFilter 2, , Sheet1.Cells(1, 10), -1
End Sub