How to get combinations and derive formulas?

Dear all,

I have a set of river discharge data from 19 dates, each containing two dependend values (water level (m) and water discharge (m³/s)). Simplyfied: A1, A2, B1, B2, ... S1, S2.
I need to derive all rating curves for all combinations of three values. I want to do that using the LINEST function, where the x-values are defined by a three-value combination of the first variable (e.g. A1, B1, S1) and the y-values correspondandly (e.g. A2, B2, S2). Therefore I need all possible combinations from variable 1 and 2, giving me 2 x 969 combinations (n=19; r=3; no repitition; order doesn't matter).
Unfortunately, the values are not fixed but normal distributed around each measured value for A - S. That means I generated random numbers (latin hyper cube) which are in an extra excel sheet, one column per measured value (A1, A2, B1, etc.). So, for the functions I want to use unique values.

Is there a way to generate the 2 x 969 combinations automatically?
Can excel also give me unique values from the random numbers sheet for each combination? (for example: A1i, B1i, C1i & A2i, B2i, C2i; next A1ii, B1ii, D1ii & A2ii, B2ii, D2ii. In the existing random number sheet the values (e.g. A1) are in columns with a large number of random numbers in rows beneath (i, ii, iii, ...).
And would it be possible to arrange it in such a way that later I only need to pull down the LINEST function next to the combinations to obtain my 969 rating curve functions?

I hope my issue is comprehensible, but I have to admit that it is fairly complex. At least it reaches far beyond my excel knowledge...
Thanks for your help!

Hi Kaper, thank you for your help! I've never worked with macros in excel before and was pretty fascinated by that simple script.
However, I can't get it to do exactly what I need. I attatched a workbook for demonstration. It is important that in each combination the values are unique. Therefore I created a bunch of random numbers which are normally distributed around the measured values. I created them using an external program and copied them into the workbook. Each of these values (A1i, A1ii, ...) is supposed to be used only once in the combinations and I need to be able to replace them later on by copying in new values.

Could anyone help me out with a macro that fills the columns of my 969 combinations with the unique random values? Or maybe share an idea how to approach this?

All the best,
grantor

969 combinations and formulas.xlsx

Hi,
great work Kaper, thank you so much! It worked perfectly well with my 969 combinations.
I'm now trying to understand and modify the macro but have some difficulties with that. Unfortunately this is my first time having to work with VBA. My goal is to be able to alter your macro to be able to crease the number of combinations (have only A - I data sets) and also change the number of random values: When there are fewer combinations, i = 153 should be decreased to the exact amount of unique numbers needed. With n=19 I end up with 969 combinations and i=153 unique values for each data set, with n=9 I have 84 combinations and i=28 unique values if I calculated correctly.
I'd be very grateful if you or someone else could briefly outline how to adjust the macro.
Happy New Year!

Kaper, I just found out that your macro works very well, even with fewer combinations! It tells me about some error while running the macro and wants me to debug line 10, but nevertheless it works. But why does it pick the random values from the second sheet ('LHC values') bottom up? Could that be changed?
Again, thank you.

Hi there @Kaper.

I want to the same thing as requested in the initial question but after obtain all possible sets of 6 numbers combination in the 60 number serie, I need to refine within all the set of 6 possible combinations, the sets that the sum of the six stay in the range of a minimum of 180 and a maximum of 191.

It´s possible to get that with your code ?

Thanks and cheers

Hi @Kaper

I did what you said, here is my thread
http://www.excelforum.com/excel-prog...mbination.html