Formula to give all the possible combinations

are you still on XL 2007? if not - update your profile
anyway read big yellow banner at the top of this site

Hello AliGW,

I don't know what "manually mocked" means, but this spreadheet I made my self and put the few combitanions that is on it by hand.

Again, what I need is the formula to give the all possible combinations.

A formula for 2 variables (Column A with Column B, Column A with Column C and Column B with Column C) and a formula for the 3 variables.

I re-attach the file without the comments.


Thank you for your attention.


Data-Eduardo_v1.xlsx

with duplicates

Data 2	Data 1	Count	Sum
E	B	20	101238.84
E	A	17	100415.56
E	C	28	16357.36
F	B	20	106794.63
F	A	17	105926.17
F	C	28	17255.02
D	B	20	120992.76
D	A	17	120008.84
D	C	28	19549.04

without duplicates

Data 2	Data 1	Count	Sum
E	B	20	-920.69
E	A	17	-799.71
E	C	28	-1308.26
F	B	20	-920.69
F	A	17	-799.71
F	C	28	-1308.26
D	B	20	-920.69
D	A	17	-799.71
D	C	28	-1308.26

which one?

Thank you Sandy666.

But I don't understand what you mean with or without duplicates. From what I see the count of each combination are the same but I don't see how you came with that sum values.

because above are counted distinct rows but here - all rows (with duplicates)

Data 1	Data 2	Count	Sum
B	E	30668	109308.98
B	F	32351	29710.56
B	D	36652	109567.04
A	E	28044	99956.34
A	F	29583	27168.48
A	D	33516	100192.32
C	E	28700	102294.5
C	F	30275	27804
C	D	34300	102536

Hi Edneco,

Not sure what you are trying to achieve here. I am attaching the your previous XLS file, wondering if that solve your problem.

Cheers,
Novan

Originally Posted by HansDouwe

Could you explain your question in more detail?

In post #1 your question is about counting and finding all combinations given the letters allowed in each column.
Your example sheet is about determining a sum per given combination.

That is quite another.

The main question is to find all the possible combinations in the first place. 3 columns with 3 different variables in all collumns.

I think there are 27 possible combinations:

ADG ADH ADI AEG AEH AEI AFG AFH AFI BDG BDH BDI BEG BEH BEI BFG BFH BFI CDG CDH CDI CEG CEH CEI CFG CFH CFI

This is simple because there are a few variables. But what if I have 5/6 different variables in 10 columns?

That´s why I´m trying to find a formula to give all these combinations. See my point.

try this one

Hi Edneco,
I can see exactly what you want to achieve.

To find all possible unique combinations you need to use the "=Unique" function, like this:

Formula:

Please Login or Register  to view this content.

for the two columns, and like this for the 3 columns:

Formula:

Please Login or Register  to view this content.

. Put those formulas in your cells f2 and j2

Now you have to lists of all possible combinations in a list going down, with only 1 of each possible set of letters, e.g. "BEH".

You can easily adapt your COUNT and SUM formulas by using the "mid" function to extract the first, second or third letter.

So in your cell L3, you would replace the formula

Formula:

Please Login or Register  to view this content.

with the formula

Formula:

Please Login or Register  to view this content.

Copy and paste your formula downwards.



I attach your spreadsheet in which I have solved what you want to achieve.

Please thank and mark "solved" if this is the solution you are seeking.

Why not use PivotTable?

TRICKYT57, fantastic....

That is exactly what I want.... I´m just gonna to try if this results if I have different numbers of variables in each column (Column A - 3 variables, Column B - 2 variables, Column C - 5 variables)....


Again, thank you very much for help....