# Combination with repetitions into excel for multiple data

#### Nic91

##### New Member
Hi guys!

New tothe forum and first post for me, I tried to figure it out on my own and by looking on this forum, but not sure my mind is completely clear.

I have to say I'm quite new to this kind of statistics, so apologize if the question is actually easy!

I want to calculate the total number of possible combinations including repetitions of a set of 5 items, each one of them can assume 3 different values.

so, let's say the items are A,B,C,D and the values are 1,2,3. The combiantion may be 11111, 22222, 33333, 12222, 11222 etc.. I would like to know what is the maximum amount of this combination that can be entered and, if doable, a way to display all this combination in a list.

I tried with =COMBIN(15,3) - being 5*3 the crossing of those combinations and 3 the possible outcomes - and I get 455, which I believe it may be a low number, also, not sure if this includes repetions (ie. 11111) or not. Is it correct?

If so, how can I do to show all the possible combinations in a list?

Thanks a lot to everyone!!

##### Well-known Member
repetitions of a set of 5 items, each one of them can assume 3 different values.

so, let's say the items are A,B,C,D and the values are 1,2,3. The combiantion may be 11111, 22222, 33333, 12222, 11222 etc.. I would like to know what is the maximum amount of this combination that can be entered and, if doable, a way to display all this combination in a list.
So no zeroes? To find all arrangements:

and drag down....(3^5 = 243)

But there will be repeating numbers and repeating groups (disregarding order)

PERMUT gives you repeating numbers but nonrepeating groups (disregarding order)

COMBIN gives you nonrepeating numbers and nonrepeating groups (disregarding order)

Last edited:

#### Nic91

##### New Member
So no zeroes? To find all arrangements:

Excel 2010
ABCDE
111111

</tbody>
Sheet2

Worksheet Formulas
CellFormula
A1=MOD(ROUNDUP(ROW(A1)/81,0)-1,3)+1
B1=MOD(ROUNDUP(ROW(A1)/27,0)-1,3)+1
C1=MOD(ROUNDUP(ROW(A1)/9,0)-1,3)+1
D1=MOD(ROUNDUP(ROW(A1)/3,0)-1,3)+1
E1=MOD(ROW(A1)-1,3)+1

</tbody>

<tbody>
</tbody>

and drag down....(3^5 = 243)

But there will be repeating numbers and repeating groups (disregarding order)

PERMUT gives you repeating numbers but nonrepeating groups (disregarding order)

COMBIN gives you nonrepeating numbers and nonrepeating groups (disregarding order)

thanks a lot Sheetspread - will definitely try the above!

##### Well-known Member
Cont'd:

you can find all permutations by ordering with a small() formula, then deleting duplicates

you can find all combinations with a FREQUENCY or COUNTIF formula and deleting the ones with repeating 1s,2s,3s....

#### Nic91

##### New Member
got it - thanks!

I already tried the answer you gave me above and it works perfectly, I have all the possible combinations as I wanted.

Just a question, what's the logic behind the formulas you sent me? Let's say that tomorrow I want to do the same but the numbers are, for example, 5, 10, 15, 20 - should I change the fina part with -5,15)+5 ?

#### B___P

##### Active Member
With a little change
in A1 and then copy

=MOD(ROUNDUP(ROW(B3)/(3^(COLUMN()-1)),0)-1,3)+1

Bye

##### Well-known Member
I have all the possible combinations as I wanted.
Well tuples really

Just a question, what's the logic behind the formulas you sent me? Let's say that tomorrow I want to do the same but the numbers are, for example, 5, 10, 15, 20 - should I change the fina part with -5,15)+5 ?
B__P's method works (changes the base and the power) or you can use shg's popular chinese food menu:

https://app.box.com/s/47b28f19d794b25511be

#### B___P

##### Active Member
In my post there was a typo.
Assuming you start in A1 the correct formula is

=MOD(ROUNDUP(ROW(A1)/(3^(COLUMN()-1)),0)-1,3)+1

Where 3 counts the elements you shuffle

For your last question you have to put together
=MOD(ROUNDUP(ROW(A1)/(4^(COLUMN()-1)),0)-1,4)+1
4 because 5, 10, 15, 20 are 4 elements

and

=CHOOSE(1,5,10,15,20)
where 1 is the index and the rest is the list of elements to shuffle

the result is

=CHOOSE((MOD(ROUNDUP(ROW(A1)/(4^(COLUMN()-1)),0)-1,4)+1),5,10,15,20)

Bye

1,082,360
Messages
5,364,920
Members
400,815
Latest member
Joaquin Phoenix

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...