# How to Find Combinations Without Repetition in Excel

Get FREE Advanced Excel Exercises with Solutions!

Combinations in mathematics is a concept that is quite important in various areas of mathematics, particularly in probability and statistics. It is a very useful and important technique that can be used to determine the number of ways that a set of objects can be combined or arranged when order does not matter. We can use combinations without repetition in Microsoft Excel too. In this article, I am going to explain 3 smart ways on the topic of how to find combinations without repetition in Excel. I hope it will be helpful for you if you are looking for a similar sort of thing.

## 3 Smart Ways to Find Combinations Without Repetition in Excel

Among many possible solutions, I am going to explain 3 smart ways to find combinations without repetition in Excel. They are described below.

### 1. Use COMBIN Function in Excel

In order to find the possible number of combinations, the COMBIN function can be used. The important fact here is that it just gives the number of combinations, but the actual combinations.

Steps:

• Create an organized dataset. Here, I have created a dataset with the Candidate No and Name columns. • Now, input the following formula to find the number of combinations.
`=COMBIN(6,4)` • Press ENTER to have the desired output. ### 2. Employ a Formula to Get Combination Without Repetition

We can employ a combined formula to find the combinations of the objects. The best part is that we will have all the possible combinations in this process, unlike the previous method which only shows the total number of combinations.

Steps:

• Input the following formula to have the combination output.
`=IF(ROW()-ROW(\$B\$13)+1>COUNTA(\$B\$5:\$B\$8)*COUNTA(\$C\$5:\$C\$7),"",INDEX(\$B\$5:\$B\$8,INT((ROW()-ROW(\$B\$13))/COUNTA(\$C\$5:\$C\$7)+1))&"-"&INDEX(\$C\$5:\$C\$7,MOD(ROW()-ROW(\$B\$13),COUNTA(\$C\$5:\$C\$7))+1))` • Next, hit the ENTER button to have the output.  ### 3. Use Excel VBA to Avoid Repetition of Combination

The smartest way to have the combinations is to use the VBA. For more clarification, I will use a dataset with the Name, Position, and Club columns. In the following section, we will learn the whole procedure. Steps:

• Go to the Developer tab first.
• Next, click on Visual Basic from the ribbon. • After that, click on Insert.
• From the available options, pick Module. • Now, write the following code to have the desired output.
``````Sub CombinationsWithoutRepitition()
Dim Name, Position, Club As Range
Dim Rng  As Range
Dim Strng As String
Dim FN1, FN2, FN3 As Integer
Dim SV1, SV2, SV3 As String
Set Name = Range("B5:B10")
Set Position = Range("C5:C10")
Set Club = Range("D5:D10")
Strng = "-"
Set Rng = Range("F5")
For FN1 = 1 To Name.Count
SV1 = Name.Item(FN1).Text
For FN2 = 1 To Position.Count
SV2 = Position.Item(FN2).Text
For FN3 = 1 To Club.Count
SV3 = Club.Item(FN3).Text
Rng.Value = SV1 & Strng & SV2 & Strng & SV3
Set Rng = Rng.Offset(1, 0)
Next
Next
Next
End Sub`````` • Finally, press on the F5 button or click on Run to have all the possible combinations. ## Conclusion

At the end of this article, I like to add that I have tried to explain 3 smart ways on the topic of how to find combinations without repetition in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.

## Related Articles #### Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  