Generate a list of combined columns

Question

I need to generate a list of possible outcomes given 5 unique rows of information. say A1=12, A2=34, then B1=Some, B2=sOme, B3=soMe, B4=somE, then C1=56, C2=78

you get the idea all with the final row being E and all the outcomes will be shown in row F

I just want to show unique outcomes, how could do I go this in either Google Sheets or Excel?

this is an expansion of a previously answered question here

Generate all possible combinations for Columns in Google SpreadSheets

Do you consider the values in column B to be unique? Or duplicates? — JvdV, Jun 19 '19 at 10:32
shared sheet https://docs.google.com/spreadsheets/d/1aM-VPH_-ftWm3OE7hKOe941lr6331_0EytFdREM_gBM/edit?usp=sharing — Wiiliam Bonney, Jun 19 '19 at 11:38

player0 · Accepted Answer · 2019-06-19T12:38:41.573

paste in F1 cell:

=ARRAYFORMULA(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(TRANSPOSE(SPLIT(REPT(CONCATENATE(TRANSPOSE(SPLIT(REPT(CONCATENATE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))&CHAR(9)),COUNTA(C1:C)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(C1:C, C1:C<>"")&CHAR(9),COUNTA(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))))),CHAR(9)))&CHAR(9)),COUNTA(D1:D)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(D1:D, D1:D<>"")&CHAR(9),COUNTA(TRANSPOSE(SPLIT(REPT(CONCATENATE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))&CHAR(9)),COUNTA(C1:C)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(C1:C, C1:C<>"")&CHAR(9),COUNTA(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))))),CHAR(9)))))),CHAR(9)))&CHAR(9)),COUNTA(E1:E)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(E1:E, E1:E<>"")&CHAR(9),COUNTA(TRANSPOSE(SPLIT(REPT(CONCATENATE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))&CHAR(9)),COUNTA(C1:C)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(C1:C, C1:C<>"")&CHAR(9),COUNTA(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))))),CHAR(9)))&CHAR(9)),COUNTA(D1:D)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(D1:D, D1:D<>"")&CHAR(9),COUNTA(TRANSPOSE(SPLIT(REPT(CONCATENATE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))&CHAR(9)),COUNTA(C1:C)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(C1:C, C1:C<>"")&CHAR(9),COUNTA(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))))),CHAR(9)))))),CHAR(9)))))),CHAR(9))))

player0 - that gave me an error " #VALUE " in google sheets, is that only for excel ?? — Wiiliam Bonney, Jun 19 '19 at 11:41
@WiiliamBonney no, this is for google sheets. answer updated — player0, Jun 19 '19 at 12:38
i have run into an issue now where it returning #value and saying " Text result of REPT is longer than limit of 32000 characters ". is there a way around this on google sheets ? — Wiiliam Bonney, Jun 21 '19 at 02:32
try it in this sheet: https://docs.google.com/spreadsheets/d/1o-XAMwR7oeLowqjqykHGYwNqoptWzX3XPLiepIpgcUw/copy — player0, Jun 21 '19 at 10:20
im getting a value error.. here is a copy of the sheet https://docs.google.com/spreadsheets/d/1rxDpf9A6srOqr2vzf6YPT42q7lhhnvevQNdCKWWTOps/edit?usp=sharing — Wiiliam Bonney, Jun 21 '19 at 11:36
i tried using the previous sheet but the REPT error came back.. shared sheet https://docs.google.com/spreadsheets/d/1mr0YBrMpJykCWll4zLYExTDYdPFRDUqs1FWEeSoTOkY/edit?usp=sharing — Wiiliam Bonney, Jun 21 '19 at 11:38
well, it looks like we hit the google sheets limit. your dataset is just enormous. if my math is correct the output would be **4.1943E+15** variations which is impossible to achieve in google sheets (perhaps even impossible for any publicly avail software) — player0, Jun 21 '19 at 12:39
OH... well back to the drawing board then... im trying to make a spreadsheet that given certain words, numbers and symbol could create a password list to through at a zip file with a forgotten password... — Wiiliam Bonney, Jun 21 '19 at 14:08

score 0 · Answer 2 · answered Jun 19 '19 at 13:42

For Excel

With data in columns A through E like:

Run this short VBA macro:

Sub Kombos()
    Dim N(1 To 5) As Long, i As Long
    Dim a As Long, b As Long, c As Long, d As Long, e As Long
    With Application.WorksheetFunction
        For i = 1 To 5
            N(i) = .CountA(Columns(i).Cells)
        Next i
    End With

    i = 1
    For a = 1 To N(1)
    For b = 1 To N(2)
    For c = 1 To N(3)
    For d = 1 To N(4)
    For e = 1 To N(5)
    Cells(i, 6).Value = Cells(a, 1) & Cells(b, 2) & Cells(c, 3) & Cells(d, 4) & Cells(e, 5)
    i = i + 1
    Next e
    Next d
    Next c
    Next b
    Next a
End Sub

To produce:

Generate a list of combined columns

2 Answers2

Linked