Different combinations of cells should take a specific value

[Aleksei]

Hi all,

I have a column of data (10 cells) and I need to do the following: I want different combinations of cells in each following column take a value of "0". So in the first 10 columns - every different cell takes "0" value, then in the following columns there must be already 2 cells with "0" value (all the possible combinations of 10), then the number of "0" values in each combination increases to 3 and etc. (till 9 of them are equal to "0").
Do you guys know how one could do it in Excel? Is there maybe some formula or code in VBA?

Thank you in advance.

Best regads,
Aleksei

 

[Keebellah]

Hi Aleksei,
What are you doing? Playing Sudoku?

 

[Aleksei]

Hi,

haha not really. I'm trying to do Mean-Variance Analysis. There I have to assign weight to each asset in a portfolio. My aim is to consider portfolios with all the possible combinations of assets. I want to gradually exclude assets from the list (assign "0" weight to them). That's what I'm doing

 

[Keebellah]

I can't help you right now, am about to leave for a fortnight
A tip, try using the WorksheetFunction.COUNTIF() for each row and count the ocurrences of "0" or 0 that would help with the 2 3 4 etc allowed.
HAppy coding.

 

[Aleksei]

Thank you for your tip. Might be good for start.