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.


How to Find Combinations Without Repetition in Excel: 3 Smart Ways

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.

 Excel Combinations Without Repetition

  • Now, input the following formula to find the number of combinations.
=COMBIN(6,4)

Use COMBIN Function

  • Press ENTER to have the desired output.

Read More: How to Generate All Possible Combinations of a Set of Numbers in Excel


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))

Employ a Formula

  • Next, hit the ENTER button to have the output.

  • Use Fill Handle to AutoFill the rests.

Excel Combinations Without Repetition


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.

Run a VBA Code

Steps:

  • Go to the Developer tab first.
  • Next, click on Visual Basic from the ribbon.

Excel Combinations Without Repetition

  • 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.

Excel Combinations Without Repetition


Download Practice Workbook


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


<< Go Back to Excel COMBIN Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly 2 years. Currently serving as an Excel and VBA Content Developer, Arif has authored over 120 articles. His expertise lies in Microsoft Office Suite, and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and ongoing enthusiasm for expanding his knowledge in data analysis.

Designation

Excel &... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo