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

How to Get All Combinations of 2 Columns in Excel (4 Easy Ways)

In this article, we will discuss how to get all combinations of 2 columns in Excel. Users often need to combine values of two columns to get an output. The combined output can be the representative of both the columns. It also allows users to permutate and get all the possible values possible from the combinations.


Download Practice Workbook

You can download the practice workbook here.


4 Ways to Get All Combinations of 2 Columns in Excel

In this article, we will talk about 4 ways to get all combinations of 2 columns in Excel. Firstly, we will use the Power Query feature to get the job done. Secondly, we will combine the IF, ROW, COUNTA, and INDEX functions together to form a formula. Thirdly, we will use helper rows and combined functions to do the task. Finally, we will resort to a VBA Code to get all the possible combinations of 2 columns in Excel.

1. Using Excel Power Query Editor to Combine 2 Columns

The Power Query is a very powerful feature in Excel. It allows users to organize and manipulate data according to their desire. In this method, we will use this feature to merge two columns and get all the combinations of their values.

Steps:

  • Firstly, select the B5 cell in the Gender column.
  • Secondly, go to the Insert tab.
  • Finally, select Table.
  • As a result, a prompt will be on the screen.

inserting table to get all combinations of 2 columns in excel

  • In the prompt, select the B5:B6 as the table range and click on
  • As a result, the range will be converted into a table.

selecting table range to get all combinations of 2 columns in excel

  • Repeat the process for the Category column and turn it into a table as well.

  • Now, select the B5 cell and go to the Data tab.
  • From the Data tab, select From Table/Range.

opening power query to get all combinations of 2 columns in excel

  • Consequently, the column will be opened in the Power Query window.

  • In the Power Query window, first, go to the Home tab.
  • Then, select Close & Load To from the Close & Load option.
  • Consequently, a prompt will be on the screen.

applying close and load to command to get all combinations of 2 columns in excel

  • From the prompt, first, choose the Only Create Connection option.
  • Then, click on OK.

creating connection of tables to get all combinations of 2 columns in excel

  • Consequently, a connection table sign will appear on the right side of the sheet.

  • Repeat the same steps for the Category table.
  • As a result, there will be two table connections.

  • Now, choose Table4.
  • As a result, the Power Query window will be opened.

  • In the Power Query window, first, go to the Add Column.
  • Then, select Custom Column.

  • Then, in the Custom Column prompt, write Table5.
  • Next, click OK.

inserting columns using custom column to get all combinations of 2 columns in excel

  • As a result, the Custom column will be added beside the Gender column.
  • Now, select the double arrow sign to the top right corner of the column.

  • Next, select OK from the outstretching window.

  • Consequently, the Category column will appear beside the Gender column.

  • After that, go to the Add Column tab.
  • From there, select Merge Columns.
  • Consequently, a prompt will be on the screen.

merging two columns to get all combinations of 2 columns in excel

  • In the prompt, first, select Custom.
  • Secondly, type “-” as the separator.
  • Thirdly, rename the merged column as Products.
  • Finally, choose OK.

selecting separators to get all combinations of 2 columns in excel

  • As a result, we will have all the combinations of the 2 columns in Power Query.

using power query to get all combinations of 2 columns in excel

  • Now, open a new worksheet.
  • Then, go to the Data tab.
  • From there, select Queries & Connections.
  • As a result, the list of tables will appear on the right side of the sheet.

  • After that, choose the B4 cell.
  • Then, right click on one of the tables.
  • Fianlly, from the available options, select Load To.
  • Consequently, a prompt will appear on the screen.

  • From the prompt, first, select Table.
  • Then, select the B4 cell as the cell to insert the table.
  • Fianlly, click OK.

  • Consequently, we will get all the combinations of the 2 columns.

P:S- Here, we could not use the Close & Load command to load the Power Query table in Excel sheet because we created the tables by applying Connection Only. So, we had to transform it into table before inserting it into an Excel sheet.

Read More: How to Create All Combinations of 4 Columns in Excel (2 Ways)


2. Applying Combined Excel Functions

In this instance, we will combine the IF, ROW, COUNTA, INDEX, and MOD functions to form a gigantic formula. However, the purpose of this formula is very simple. We will use the formula to get all the combinations of 2 columns.

Steps:

  • To begin with, click on the D5 cell and enter,
=IF(ROW()-ROW($D$5)+1>COUNTA($B$5:$B$6)*COUNTA($C$5:$C$7),"", INDEX($B$5:$B$6,INT((ROW()-ROW($D$5))/COUNTA($C$5:$C$7)+1))&"-"&INDEX($C$5:$C$7,MOD(ROW()-ROW($D$5),COUNTA($C$5:$C$7))+1))
  • Then, press Enter.

inserting formula to get all combinations of 2 columns in excel 

Formula Breakdown

  • ROW()-ROW($D$5)+1>COUNTA($B$5:$B$6)*COUNTA($C$5:$C$7): Here, the ROW() returns the value of the row of the D5 cell which is So as the ROW($D$5). Now, the formula becomes 5-5+1>COUNTA($B$5:$B$6)*COUNTA($C$5:$C$7). The COUNTA($B$5:$B$6) and COUNTA($C$5:$C$7) returns the number of non-empty cells in the range B5:B6 and C5:C7 which are 2 and 3 respectively. So the expression becomes 1>2*3 or 1>6 which is False.
    • Output: False
  • INDEX($B$5:$B$6,INT((ROW()-ROW($D$5))/COUNTA($C$5:$C$7)+1)): The (ROW()-ROW($D$5) returns The COUNTA($C$5:$C$7) expression counts the number of non-empty cell in the C5:C7 range and returns 3. So, the expression becomes INDEX($B$5:$B$6,INT(0/3+1)) and will become INDEX($B$5:$B$6,INT(0+1)) or INDEX($B$5:$B$6,INT(1)) . The INT function will return 1. The final expression will be INDEX($B$5:$B$6,1). Finally, the INDEX function will return the 1st value of the B5:B6 range which is Male.
    • Output: Male
  • INDEX($C$5:$C$7,MOD(ROW()-ROW($D$5),COUNTA($C$5:$C$7))+1)): The MOD(ROW()-ROW($D$5),COUNTA($C$5:$C$7))+1 will become MOD(0,3)+1. The MOD function will return the remainder of the division between 0 and 3 which is So, the INDEX($C$5:$C$7,MOD(ROW()-ROW($D$5),COUNTA($C$5:$C$7))+1)) becomes INDEX($C$5:$C$7,0+1)) or INDEX($C$5:$C$7,1)). Thus, the expression will return the first value of the cell range C5:C7 and that will be Jacket.
    • Output: Jacket
  • IF(ROW()-ROW($D$5)+1>COUNTA($B$5:$B$6)*COUNTA($C$5:$C$7),””, INDEX($B$5:$B$6,INT((ROW()-ROW($D$5))/COUNTA($C$5:$C$7)+1))&”-“&INDEX($C$5:$C$7,MOD(ROW()-ROW($D$5),COUNTA($C$5:$C$7))+1)): This expression becomes IF(False,#NA, “Male” &”-“& “Jacket”). So, the expression is asking to concatenate the Male , – , and Jacket if the condition is
    • Output: Male-Jacket

  • As a result, we will have the first combination of the two columns.
  • Now, lower the cursor down to autofill the rest of the cells.

Read More: How to Apply All Combinations of 3 Columns in Excel


3. Using Helper Columns and Combined Functions

In this example, we use different functions like the COUNTA, QUOTIENT, MOD, ROW, IF, COUNTIF, and INDEX functions in different columns and cells to arrange our data. Finally, we will use the CONCATENATE function to combine the two columns and get all the possible combinations.

Steps:

  • To start with, choose the C9 cell and type,
=COUNTA(B5:B6)
  • Then, hit Enter.
  • As a result, we will have the counting of the values in the Gender column.

counting values of gender column to get all combinations of 2 columns in excel

  • Next, select the C10 cell and type,
=COUNTA(C5:C7)
  • Hit Enter.
  • Consequently, we will get the number of values under the Category column.

  • Next, choose the E5 cell and enter the following,
=QUOTIENT(ROW(E5)-4,$C$10)+IF(MOD(ROW(E5)-4,$C$10)=0,0,1)
  • Then, press Enter.

 getting gender row number to get all combinations of 2 columns in excel

Formula Breakdown

  • QUOTIENT(ROW(E5)-4,$C$10): The ROW(E5)-4 expression will become 5-4 or So the overall expression be QUOTIENT(1,$C$10) or QUOTIENT(1,3). The QUOTIENT function will return the integer part of the division of 1 by 3 that is 0.
    • Output: 0
  • IF(MOD(ROW(E5)-4,$C$10)=0,0,1): The ROW(E5)-4 expression will return The MOD(ROW(E5)-4,$C$10) will become MOD(1,3) which returns 1. So, the overall expression will be IF(1=0,0,1) or IF(False,0,1). Finally, the formula will return 1.
    • Output: 1
  • QUOTIENT(ROW(E5)-4,$C$10)+IF(MOD(ROW(E5)-4,$C$10)=0,0,1): The entire expression will become 0+1 or
    • Output: 1

 

  • As a result, we will assign the row number for the first value of the Gender column.
  • Then, move the cursor down autofill the rest of the cells.

  • Next, click on the F5 cell and type the following,
=COUNTIF($E$5:E5,E5)
  • Press Enter. 

  • Consequently, we will get the row number for the first value of the Category column.
  • Next, lower the cursor down to autofill.

  • Thereafter, choose the G5 cell and insert,
=INDEX($B$5:$B$6,E5)
  • Then, press the Enter button.

  • As a result, we will have the first value of the Gender column associated with the value in the Gender Row.
  • Finally, lower the cursor to autofill the rest of the cells.

  • After that, choose the H5 cell and type,
=INDEX($C$5:$C$7,F5)
  • Then, press Enter.

  • Consequently, we will have the first value of the Category column associated with the value in the Category Row.
  • Move the cursor down to the last cell to autofill.

  • Finally, choose the I5 cell and enter the following formula,
=CONCATENATE(G5,"-",H5)
  • Then, press Enter.

using concatenate function to get all combinations of 2 columns in excel

  • As a result, we will have our first combination of the 2 columns.
  • Finally, lower the cursor to autofill the rest of the cells.


4. Applying VBA Code

In this final method, we will resort to a VBA Code to get all the combinations of 2 columns in Excel. This code will take values from each column and run them in loop to give a combined output.

Steps:

  • Firstly, go to the Developer tab.
  • From there, select the Visual Basic tab.
  • As a result, a new window will be on the screen.

opening visual basic window to get all combinations of 2 columns in excel

  • From the Visual Basic window, first, select Insert.
  • Then, choose Module from the drop-down list.
  • Consequently, a new module will appear.

  • In the module, write down the following code and save it.
Sub AllCombinationsTwoColumns()
Dim am_r1, am_r2 As Range
Dim am_r  As Range
Dim am_str As String
Dim am_int1, am_int2 As Integer
Dim am_str1, am_str2 As String
Set am_r1 = Range("B5:B6")
Set am_r2 = Range("C5:C7")
am_str = "-"
Set am_r = Range("D5")
For am_int1 = 1 To am_r1.Count
    am_str1 = am_r1.Item(am_int1).Text
    For am_int2 = 1 To am_r2.Count
        am_str2 = am_r2.Item(am_int2).Text
        am_r.Value = am_str1 & am_str & am_str2
        Set am_r = am_r.Offset(1, 0)
       Next
    Next
End Sub

writing code and saving it to get all combinations of 2 columns in excel

  • Finally, run the code by clicking on the triangular shaped green button.

  • As a result, we will have all the combinations of 2 columns in the D5:D10 cell range.

Read More: How to Show All Combinations of 5 Columns in Excel


Conclusion

In this article, we have discussed 4 ways to get all the combinations of 2 columns in Excel. These methods will allow users to get the right combination from all the combinations of the values of the 2 columns. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com and unlock a great resource for Excel-related content.


Related Articles

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo