Combination is a common task in our day-to-day professional life in different aspects. In Microsoft Excel, we can easily show all the combinations for several columns. In this article, we are going to demonstrate 2 simple ways to show all combinations of 5 columns in Excel. If you are also curious about it, download our practice workbook and follow us.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
2 Easy Ways to Show All Combinations of 5 Columns in Excel
To demonstrate the approaches, we consider a dataset with 2 rows and 5 columns. So, we can say that our dataset is in the range of cells B5:B6.
All the operations of this article are accomplished by using Microsoft Office 365 application.
1. Using Power Query Table
In this method, we will use the Excel Power Query to show all the combinations of 5 columns in Excel. The steps of this method are shown below:
- First of all, select the range of cells B4:B6.
- Now, click on the From Table/Range option from the Get & Transform Data group in the Data tab.
- As a result, a small dialog box called Create Table will appear.
- Then, check on the My table has headers and click OK.
- Another dialog box called Power Query Editor will appear.
- After that, in the Add Column tab, click on the Custom Column option.
- The Custom Column dialog box will appear.
- In that box, input a column name. We keep it as Custom.
- Afterward, in the Custom column formula box, write down a constant number. Here, we write down 1.
- At last, click OK.
- Next, in the Home tab, click on the drop-down arrow of the Close & Load option and choose the Close & Load to command.
- As a result, the Import Data dialog box will appear.
- Now, check the Only Create Connection option and click OK.
- You will see the table will show in the Queries & Connections window.
- Similarly, follow the same procedure for the rest of the 4 columns.
- After that, in the Data tab, click on the drop-down arrow of the Get Data from the Get & Transform Data group.
- Then, choose the Combine Queries > Merge option.
- The Merge dialog box will lunch on your device.
- From the first drop-down box, click on Table1.
- As a result, it will show below the empty section.
- Next, click on the Custom column.
- Similarly, follow the same procedure for the second drop-down section.
- Then, click OK.
- The Power Query Editor will show up again.
- After that, click on the expansion icon from the Table2 column.
- Now, uncheck the Custom option and click OK.
- Afterward, select the Custom column and drag it to the last.
- Finally, in the Home tab, click on the drop-down arrow of the Close & Load option and choose the Close & Load to command.
- The Import Data dialog box will appear.
- Then, check the Only Create Connection option and click OK to save the first merge table.
- Similarly, follow the same procedure to merge the previous merge with the next table. For example, in the second case, in the Merge dialog box, the first merge table will be in the first empty box, and Table3 will be in the second box. Next, the second merge table will be in the upper box, and Table4 will be in the lower box. At last, Table5 will be in the lower.
- In the very last merge, select the Custom column and click on Remove Column from the Manage Columns group, located in the Home tab.
- In the end, click on the Load & Close option to save the combination result.
- You will notice that a new sheet will appear, and all of the combinations of our 5 columns will be there.
Thus, we can say that our procedure works perfectly, and we are able to show all the combinations of 5 columns in Excel.
2. Embedding VBA Code
Writing a VBA code can also help us to show the combination of 5 columns in the same Excel spreadsheet. We are going to use our previous dataset and the result will be in column H. The process is explained below step by step:
- To start the approach, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ for opening the Visual Basic Editor.
- As a result, a dialog box will appear.
- Now, in the Insert tab on that box, click the Module option.
- Then, write down the following visual code in that empty editor box.
Sub List_All_Combinations() Dim Column_1, Column_2, Column_3, Column_4, Column_5 As Range Dim Result As Range Dim X As String Dim Col_1, Col_2, Col_3, Col_4, Col_5 As Integer Dim a, b, c, d, e As String Set Column_1 = Range("B5:B6") Set Column_2 = Range("C5:C6") Set Column_3 = Range("D5:D6") Set Column_4 = Range("E5:E6") Set Column_5 = Range("F5:F6") X = "-" Set Result = Range("H5") For Col_1 = 1 To Column_1.Count a = Column_1.Item(Col_1).Text For Col_2 = 1 To Column_2.Count b = Column_2.Item(Col_2).Text For Col_3 = 1 To Column_3.Count c = Column_3.Item(Col_3).Text For Col_4 = 1 To Column_4.Count d = Column_4.Item(Col_4).Text For Col_5 = 1 To Column_5.Count e = Column_5.Item(Col_5).Text Result.Value = a & X & b & X & c & X _ & d & X & e Set Result = Result.Offset(1, 0) Next Next Next Next Next End Sub
- After that, press ‘Ctrl+S’ to save the code.
- Now, close the Editor tab.
- Afterward, in the Developer tab, click on the Macros option.
- A new dialog box called Macros will appear. Select the List_All_Combinations option.
- Finally, click on the Run button to run this code.
- You will see that all the combinations will be in our desired column.
Finally, we can say that our visual code worked successfully, and we are able to show all the combinations of 5 columns in Excel.
That’s the end of this article. I hope that this article will be helpful for you and you will be able to show all the combinations of 5 columns in Excel. Please share any further queries or recommendations with us in the comments section below if you have any other questions or suggestions.
Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!