Naimul Hasan Arif
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.
Download Practice Workbook
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.
Read More: How to Find All Combinations of 1 Column in Excel (2 Handy Ways)
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.
- Use Fill Handle to AutoFill the rests.
Read More: How to Sum All Possible Combinations in Excel
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.
Read More: How to Apply All Combinations of 3 Columns in Excel
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.
