How to generate all possible combinations of two lists (without MACRO)?

[erangai]

Hi everyone,

I need to make a list of all possible combinations of two lists as shown in the following example.
i.e. Basically when I update Column A & B, the combinations should get appear in the “Output” column.
Also need to do this without running a MACRO.

Can someone kindly help me?

Example

Column A	Column B		Output (Combinations)
Mar	AA		MarAA
Apr	BB		MarBB
May	CC		MarCC
	DD		MarDD
			AprAA
			AprBB
			AprCC
			AprDD
			MayAA
			MayBB
			MayCC
			MayDD

 

[mickael28]

Love this trick.

Simple and elegant. Did exactly what I needed, even with multiple columns.

Hello,

Could you please let me know how you managed to run an SQL in excel?

And out of curiosity, is it possible to run such queries in google spreadsheets as well, or just in microsoft excel software?

Thank you!

 

[oursoul2]

Hi Everyone,

I have a very similar question to the one posted. Your help would be very much appreciated.

Column A Column B Column C
April 10 K
May 11 L
June 12
13

I need to create an output column with all possible output between 3 different columns maintaining the column order stable across all combinations such as in the example above the output column should look as the one below. Do you know if is it possible without Macro, just using an excel formula? I have tried following the reply of the original post without luck

April 10 K
April 11 K
April 12 K
April 13 K
May 10 K
May 11 K
May 12 K
May 13 K
June 10 K
June 11 K
June 12 K
June 13 K
April 10 L
April 11 L
April 12 L
April 13 L
May 10 L
May 11 L
May 12 L
May 13 L
June 10 L
June 11 L
June 12 L
June 13 L


I hope you can help

Thanks !

 

[Eric W]

If your columns are A, B, and C, then put this charming function in F1 (or some other column on the first row):

=IF(ROW()<=COUNTA(A:A)*COUNTA(B:B)*COUNTA(C:C),INDEX(A:A,MOD(ROW()-1,COUNTA(A:A))+1)&" "&INDEX(B:B,MOD(INT((ROW()-1)/COUNTA(A:A)),COUNTA(B:B))+1)&" "&INDEX(C:C,INT((ROW()-1)/(COUNTA(A:A)*COUNTA(B:B)))+1),"")

This works for 3 columns, and automatically adjusts for different counts in those columns. The trick basically is figuring out the indexes into each column using a combination of MODs and INTs.

 

[Eric W]

Of course you need to copy that down to the cells below F1.

 

[oursoul2]

Thanks Eric! You are fantastic! I would hug you!

What I found difficult to understand is the logic you apply when creating indexes. Why you create certain index in a certain way. Could you please help me to understand the logic behind so I will enlarge my knowledge?

For example what I want to do is then repeat the whole sequence a certain number of time. Let's say 2 (3 or 4...) for example. The formula will become as below but it doesn't work because of the index of column C that doesn't loop. I understand why is not working but I am not able to adjust the column C index properly. How can I make sure it works for any number of time the sequence is repeated?

=IF(ROW()<=2*COUNTA(A:A)*COUNTA(B:B)*COUNTA(C:C),INDEX(A:A,MOD(ROW()-1,COUNTA(A:A))+1)&"-"&INDEX(B:B,MOD(INT((ROW()-1)/COUNTA(A:A)),COUNTA(B:B))+1)&"-"&INDEX(C:C,INT((ROW()-1)/(COUNTA(A:A)*COUNTA(B:B)))+1),"")

At the same time I was able to add a fourth column and re-adjust the indexes. The formula below works, but only when column D has only 2 options available because of Index of column C doesn't loop in the right way.

=IF(ROW()<=COUNTA(A:A)*COUNTA(B:B)*COUNTA(C:C)*COUNTA(D:D),INDEX(A:A,MOD(ROW()-1,COUNTA(A:A))+1)&"-"&INDEX(B:B,MOD(INT((ROW()-1)/COUNTA(A:A)),COUNTA(B:B))+1)&"-"&INDEX(C:C,INT((ROW()-1)/(COUNTA(A:A)*COUNTA(B:B)*COUNTA(D:D)))+1)&"-"&INDEX(D:D,MOD(ROW()-1,COUNTA(D:D))+1),"")

I think what I missing here is the logic that stays behind index creation to make me able apply the formula in different contexts.

I hope you can help

Thanks a million

 

[Eric W]

I'm a big fan of hugs!

The logic behind the indexes isn't all that hard, although maybe one more column would have made it easier to see the pattern. It is tricky because I only have one variable to work with (ROW), and the constants that denote the length of each column (COUNTA(A:A) etc.).

Look at the 1st column in the results column. If your elements are April, May, and June, you can see that April, May, and June repeat in that order the entire length of the results column. This is pretty much the classic MOD function (look it up if you're unfamiliar with it). So the basic formula is MOD(ROW(),COUNTA(A:A)) for the index. When ROW=COUNTA, the MOD function returns a 0, which can't be used in the index function, so I add 1 to it: MOD(ROW(),COUNTA(A:A))+1. And so that the order goes April, May, June, and not May, June, April, I subtract 1 from the row giving: MOD(ROW()-1,COUNTA(A:A))+1. This "-1" "+1" technique is repeated for all subsequent indexes, so I won't repeat the explanation.

For the second index, in your example, you have 10,11,12,13. Note that the 10 occurs 3 times, then 11 occurs 3 times, then 12 occurs 3 times, then 13 occurs 3 times, then that pattern repeats for the rest of the results. The "3 times" happens to be the number of elements in column A. So you want to start the index at 1, and only increment it every 3 rows. Once you get past the end of your pattern (3 * 4) +1 = 13, you want to start at 1 again. So we use the INT function to only increment it every 3 rows. If you put =INT((ROW()-1)/3)+1 in the first row of a worksheet and copy it down, it will go 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4, etc. Now we put the COUNTA(A:A) in place of the 3, and we wrap it in a MOD function to reset it to 1 at the end of the pattern, and we get: MOD(INT((ROW()-1)/COUNTA(A:A)),COUNTA(B:B))+1.

Whew! Getting long winded here, but we're getting close. For the third term, notice that each element from column C repeats 12 times (3 * 4). We can actually reuse the index formula from the second column, with 2 changes: MOD(INT((ROW()-1)/(COUNTA(A:A)*COUNTA(B:B))),COUNTA(C:C))+1. You divide by the product of the 1st two columns, and take the MOD of the 3rd column. In the original version, I did not use the MOD function because the IF at the start rendered it unnecessary, but it doesn't hurt and helps you see the pattern.

If you add a column D, the index would be: MOD(INT((ROW()-1)/(COUNTA(A:A)*COUNTA(B:B)*COUNTA(C:C))),COUNTA(D:D))+1

You should be able to see the pattern from there.

Good luck!

 

[mistahong]

First of all I appreciate all the posts here and they are just bomb.

I have successfully used and applied the code:

=IF(ROW()<=COUNTA(B:B)*COUNTA(C:C)*COUNTA(D:D),INDEX(B:B,MOD(ROW()-1,COUNTA(B:B))+1)&" "&INDEX(C:C,MOD(INT((ROW()-1)/COUNTA(B:B)),COUNTA(C:C))+1)&" "&INDEX(D:D,INT((ROW()-1)/(COUNTA(B:B)*COUNTA(C:C)))+1),"")

And it works wonders!

However I just have one question I couldn't find an answer to.

I am building SKU's for variations, and my supposed columns A, B, and C are generally indexed in based on conditions, and not typed in manually.

So pretty much from row 1 to 255 of columns A, B and C I have codes in there that =IF a color is present on another sheet, to bring it in and if not, to leave blank (=IF(CELL="","",CELL) all the way to row 255)

But I see that the code above pulls in blank cells for as long as another function is within the code, even if invisible. So its bringing me 255*255*255= results.

Is there a way to make this ignore blank returned cells?

The only thing I can do is to manually select empty cells and hit delete on them. Then it works fine, but was wondering if I could just automate it.

Thank you in advance for any help at all! Even if to confirm this cannot be done

A............B............C..............F
1............a............3............1a3
2............b............4............1a4
""...........c............""...........1a
""...........d............""...........1a
""...........e............""...........1a
....
...
..
.
For as many rows as C has.

P.s. "" are actually blank. Cannot see anything until the cell is clicked on to reveal the code.

 

[Eric W]

Yes, it is possible to do. I even started to develop the formulas . . . then I came to my senses! Formulas like these are a good way to learn Excel, or as an intellectual exercise, but they are really not suited for genuine work. They are too big, too complicated, and not very flexible. Something like this can be done much better with a macro. I developed one that should do what you want.

1) Open Excel.
2) Press Alt-F11 to open the VBA editor.
3) Select Insert --> module from the menu.
4) Paste in the following code:

Sub Combos()
Dim Element(), Index()
Dim MyCols As Variant, MySheet As Worksheet, OneCol As Boolean
Dim r As Long, c As Long, ctr As Long, mysize As Long
Dim delim As String, OutputCol As String, str1 As String

' Set up conditions
    Set MySheet = Sheets("Sheet1")
    MyCols = Array("A", "B", "C")
    OutputCol = "F"
    OneCol = True
    delim = " - "
    
' resize the arrays
    ReDim Element(255, UBound(MyCols))
    ReDim Index(UBound(MyCols))
    
' Read the elements
    For c = 0 To UBound(MyCols)
        Element(0, c) = 0
        Index(c) = 1
        For r = 1 To 255
            If MySheet.Cells(r, MyCols(c)) <> "" Then
                Element(0, c) = Element(0, c) + 1
                Element(Element(0, c), c) = MySheet.Cells(r, MyCols(c))
            End If
        Next r
    Next c
    
' Clear the output columns(s), and check for the number of results
    ctr = MySheet.Cells(1, OutputCol).Column
    mysize = 1
    For c = 0 To UBound(MyCols)
        mysize = mysize * Element(0, c)
        MySheet.Columns(ctr).ClearContents
        ctr = ctr + 1
    Next c
    If mysize > 1000000 Then
        MsgBox "The number of results is too big to handle!"
        Exit Sub
    End If
    
    ctr = 0
    
' Start creating combinations
Loop1:
    ctr = ctr + 1
    str1 = ""
    Set resultcell = MySheet.Cells(ctr, OutputCol)
    For c = 0 To UBound(MyCols)
        If OneCol Then
            str1 = str1 & Element(Index(c), c) & delim
        Else
            resultcell.Value = Element(Index(c), c)
            Set resultcell = resultcell.Offset(0, 1)
        End If
    Next c
    If OneCol Then MySheet.Cells(ctr, OutputCol) = Left(str1, Len(str1) - Len(delim))
    
' Increment the indices
    For c = 0 To UBound(MyCols)
        Index(c) = Index(c) + 1
        If Index(c) <= Element(0, c) Then Exit For
        Index(c) = 1
    Next c
    If c <= UBound(MyCols) Then GoTo Loop1:
    
End Sub

5) Customize it to your needs. The first 5 executable lines (under 'Set up conditions") allow you to select which columns have data, where you want to put the results, and if you want the results to be in one column or multiple columns.
6) Return to Excel.
7) Set up your data. An empty cell, or one that has "" in it from a formula will be ignored.
8) Press Alt-F8, select Combos, the click Run.

That should do it. Good luck!

 

[mistahong]

Eric, I am speechless!

I am the formula type of guy, but still. Just basic knowledge. I do what I can to go around and apply to my needs and for all else I search online.

I am completely lost when macros are being brought up and so far I have only seen people simply posting them over.

Fact that you added those 1-8 steps allowed me to run that like I already knew!!

No errors, no nothing! It simply worked!!

Who are you working for? You deserve a raise!

 

[Eric W]

I'm glad it worked for you! I'll pass your comments on to my boss!