Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Find Combinations Without Repetition in Excel

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.

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

Employ a Formula

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

Excel Combinations Without Repetition

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.

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

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.


Related Articles

Naimul Hasan Arif

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

Leave a reply

ExcelDemy
Logo