Set xDRg3 = Range("C2:C4") 'Third column data Set xDRg2 = Range("B2:B4") 'Second column data Set xDRg1 = Range("A2:A5") 'First column data VBA code: Generate all combinations of 3 or multiple columns Sub ListAllCombinations()
Click Insert > Module, and paste the following code in the Module Window. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.Ģ. Here, I will introduce a VBA code to deal with it quickly.ġ. Maybe the above formula is somewhat difficult for you to apply, if there are multiple columns data, it will be troublesome for modifying. Then select cell D2 and drag the fill handle down to the cells until get the blank cells, and all the possible combinations have been listed based on the two lists values.
Note: In the above formula, $A$2:$A$5is the range of the first column values, and $B$2:$B$4 is the range of the second list values which you want to list all their possible combinations, the $D$2 is the cell that you put the formula, you can change the cell references to your need.Ģ. Enter or copy the below formula into a blank cell, in this case, I will enter it to cell D2, and then press Enter key to get the result, see screenshot: The following long formula can help you to list all possible combinations of two lists values quickly, please do as follows:ġ. List or generate all possible combinations from multiple lists with a powerful feature List or generate all possible combinations from three or more lists with VBA code List or generate all possible combinations from two lists with formula Maybe, you can list all the combinations one by one if there are few values, but, if there are several columns with multiple values needed to be listed the possible combinations, here are some quick tricks may help you to deal with this problem in Excel. Let’s say, I have the following two columns of data, and now, I want to generate a list of all possible combinations based on the two lists of values as left screenshot shown.