While dealing with an Excel worksheet, you may need to generate all the combinations of a certain number of columns. Finding these combinations manually would be painstaking and time-consuming. You can use the VBA macros or the Power Query Editor to do the operation. It will save time and make your work easier. Also, you can use a formula to do so. We tried every method possible to gain your understanding. You can go through the article to get a proper visualization for finding all combinations of 6 columns in Excel. So, let’s get started.
How to Generate All Combinations of 6 Columns in Excel: 2 Methods
We have taken a dataset of numbers in 6 columns. Now, we will try all the combinations of these 6 columns. You can also follow the same methods for 3/4/5 or any number of columns.
Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.
1. Applying VBA Macros
You can use VBA macros to find all the combinations of 6 columns. This code will help you to combine any number of columns. You just need to change the variable number and the function number. Please follow the below steps to use the code.
- Firstly, navigate to the Developer tab >> choose Visual Basic.
- Consequently, a window appears. Go to the Insert tab >> pick Module >> Module1.
- In the Module 1 section write up the following code.
Sub CombinationsFor6Columns() Dim X1, X2, X3, X4, X5, X6 As Range Dim RG As Range Dim xStr As String Dim FN1, FN2, FN3, FN4, FN5, FN6 As Integer Dim SV1, SV2, SV3, SV4, SV5, SV6 As String Set X1 = Range("B5:B7") Set X2 = Range("C5:C7") Set X3 = Range("D5:D7") Set X4 = Range("E5:E7") Set X5 = Range("F5:F7") Set X6 = Range("G5:G7") xStr = "-" Set RG = Range("H5") For FN1 = 1 To X1.Count SV1 = X1.Item(FN1).Text For FN2 = 1 To X2.Count SV2 = X2.Item(FN2).Text For FN3 = 1 To X3.Count SV3 = X3.Item(FN3).Text For FN4 = 1 To X4.Count SV4 = X4.Item(FN4).Text For FN5 = 1 To X5.Count SV5 = X5.Item(FN5).Text For FN6 = 1 To X6.Count SV6 = X6.Item(FN6).Text RG.Value = SV1 & xStr & SV2 & xStr & SV3 & xStr & SV4 & xStr & SV5 & xStr & SV6 Set RG = RG.Offset(1, 0) Next Next Next Next Next Next End Sub
In the above code, the X1 to X6 is the range. We have to declare the range column numbers individually. FN1 to FN6 is the integer and SV1 to SV6 is the string. RG is the range of the output cell which is H5.
Eventually, run the code with the F5 key, and you’ll get the result like the image below.
2. Employing Power Query
The Power Query is a powerful tool in Excel that can be used to solve multiple table-related problems. It can also show all the combinations of the columns you need. Though the procedure is a bit lengthy, it is handy too. See the below steps.
- Firstly, select the entire range of the dataset.
- Secondly, hover over the Data tab >> choose the From Table/Range.
- Consequently, the Create Table dialog wizard appears. Check the My table has headers.
- At this moment, the Power Query Editor appears. Select Close & Load >> Close & Load To.
- Eventually, check the Only Create Connection from the Import Data dialog box.
- Again, navigate to the Data tab >> choose Get Data >> pick Launch Power Query Editor.
- Subsequently, move to the Add Column tab >> choose Custom Column.
- Eventually, the Custom Column dialog wizard appears. Write Temp to the New column name. Put 1 to the Custom column formula. Hit OK.
- Now, from the Home tab of the Power Query Editor. Choose Merge Queries >> Merge Queries as New.
- In addition, the Merge dialog box appears. Select the Temp (whatever you named) column in both portions. Make the Join Kind as Full Outer (all rows from both).
Finally, you get all the combinations for 6 columns like the image below.
How to Get All Combinations of 3 Columns Using Formula in Excel
You can also use a formula we use here for 3 column combinations. This is a large formula, and as a result, it may not be feasible for large numbers of columns. It is possible to use formulas for any number of columns, but we are discouraging you from doing so. Because it will take lots of time to write up the code. But in case you want to use it, you can follow the below steps: We have used the IFERROR, the INDEX, the COUNTA, the ROW, and the MOD functions together here.
- Initially, go to the E5 cell and insert the formula.
B5:B7= Range of Column 1.
C5:C7= Range of Column 2.
D5:D7= Range of Column 3.
COUNTA($D$5:$D$7))+1)→ looks into cells D5 and D7 and counts the amount of information.
INDEX($B$5:$B$7,INT((ROW(1:1)-1)/((COUNTA($C$5:$C$7)→ returns a value or the reference to a value from cell D5 to D7.
INT((ROW(1:1)-1)/((COUNTA($C$5:$C$7)*(COUNTA($D$5:$D$7)))))+1)→returns an integer value after dividing the number of “rows-1” by the product of the amount of information in cell C5 to C7 and D5 to D7.
IFERROR(INDEX($B$5:$B$7,INT((ROW(1:1)-1)/((COUNTA($C$5:$C$7)*(COUNTA($D$5:$D$7)))))+1)→ evaluates the formula and returns all possible combinations.
- Press ENTER and drag it down for other cells.
Finally, you get all the combinations for 3 columns with the formula (see the image).
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
That’s all about today’s session. These are some easy methods to create combinations of 6 columns in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Thanks for your patience in reading this article.