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

Page 1 of 6 123 ... LastLast
Results 1 to 10 of 57

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

  1. #1
    New Member
    Join Date
    Aug 2012
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

     
    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
    Last edited by RoryA; Jul 28th, 2017 at 09:55 AM.

  2. #2
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    8,412
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

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

    hi,

    A Cartesian product via a query table could be used. No formulas, no code.

    Such as, if the first table (including header "Column A") is given normal defined name "A" and the second table "B", then the SQL would be
    Code:
    SELECT A.[Column A] & B.[Column B]
    FROM A, B
    Briefly. Create the defined names, save the file then create the query (ALT-D-D-N) by following the wizard. At the last step of the wizard choose the option to edit in MS Query and change the SQL to above. Exit MS Query to complete the query table. Please google for further explanation or ask again.

    regards

  3. #3
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    Suppose the A and B data start with row 1.

    Then, in some empty cell, say F1 enter the formula =IF(ROW()-ROW($F$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($F$1))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1))

    Copy F1 down col. F until you get cells that look empty.
    Last edited by RoryA; Jul 28th, 2017 at 09:56 AM.

  4. #4
    New Member
    Join Date
    Aug 2012
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Dear tusharm,

    You are a GOD. This is exactly what I was looking for. Thanks a lot.

  5. #5
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    You are welcome.
    Quote Originally Posted by erangai View Post
    Dear tusharm,

    You are a GOD. This is exactly what I was looking for. Thanks a lot.

  6. #6
    New Member
    Join Date
    Aug 2012
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by tusharm View Post
    You are welcome.
    Dear tusharm,

    I just tried to understand how you have given the formula to improve my knowledge & not to bother you again on a similar incident again (LOL!)

    I can figureout the start & the end but the part in red below is bit clueless. Can you pls explain it for everyone to improve their excel skills pls...

    IF(ROW()-ROW($E$1)+1>COUNTA(B:B)*COUNTA(C:C),"",INDEX(B:B,INT((ROW()-ROW($E$1))/COUNTA(C:C)+1))&INDEX(C:C,MOD(ROW()-ROW($E$1),COUNTA(C:C))+1))

    This is to arrive at how many times an item in the cloumn A, should repeat right?
    In my below ex, "Mar" should be repeated four times since there are four items in the cloumn B.

    So ROW()-ROW($E$1) = i.e. if you are at N th row, this gives the value of N-1 (at 10th row this gives the value 9)
    COUNTA(C:C)
    = i.e. the number of items in cloumn B list (in the below example it is 4)
    So at 10th row, (ROW()-ROW($E$1))/COUNTA(C:C)+1) means (9/4)+1 = 2.250+1 = 3.250
    So when you take only the integer this will give the answer 3, so at 10th row, the 3rd item in cloumn A (which is May) needs to get appeared.
    I understand this part but the mathematical logic behind this is what I'm not very clear about.

  7. #7
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    8,412
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

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

    I'm guessing not relevant here because it seems a formula is preferred - quite a contrast, a formula that isn't understood and the simple SQL.

    SELECT A.[Column A] & B.[Column B]
    FROM A, B


  8. #8
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    That's something I've been doing very effectively for a long time. The idea is this. If you have 2 lists that you want to pick from so that you get every possible combination, you want to select 1 item from list A and combine it with every item in list B, then select the next item in list A and combine it with every item in list B.

    The way to loop through a list is to use a function that generates 1,2,3,...,N,1,2,3,...,N,... This is close to what the MOD function does. It generates values 0,1,2,...N-1,0,1,2,...,N-1,...

    So, ROW()-ROW(1st row) will generate 0,1,2,3,... and MOD(,N) will yield 0,1,2,...N-1,0,1,2,...,N-1,... Add 1 to that and you get the desired sequence.

    Now, for list A you want to increment the index only after every item in list B has been selected, which will happen every N rows. So, INT(ROW()-ROW(1st row) / N) will jump from one integer to the next every N rows.

    You can extend this concept to any number of lists A, B, C, D,...See
    Generate All Permutations
    Generate All Permutations
    Quote Originally Posted by erangai View Post
    Dear tusharm,

    I just tried to understand how you have given the formula to improve my knowledge & not to bother you again on a similar incident again (LOL!)

    I can figureout the start & the end but the part in red below is bit clueless. Can you pls explain it for everyone to improve their excel skills pls...

    IF(ROW()-ROW($E$1)+1>COUNTA(B:B)*COUNTA(C:C),"",INDEX(B:B,INT((ROW()-ROW($E$1))/COUNTA(C:C)+1))&INDEX(C:C,MOD(ROW()-ROW($E$1),COUNTA(C:C))+1))

    This is to arrive at how many times an item in the cloumn A, should repeat right?
    In my below ex, "Mar" should be repeated four times since there are four items in the cloumn B.

    So ROW()-ROW($E$1) = i.e. if you are at N th row, this gives the value of N-1 (at 10th row this gives the value 9)
    COUNTA(C:C)
    = i.e. the number of items in cloumn B list (in the below example it is 4)
    So at 10th row, (ROW()-ROW($E$1))/COUNTA(C:C)+1) means (9/4)+1 = 2.250+1 = 3.250
    So when you take only the integer this will give the answer 3, so at 10th row, the 3rd item in cloumn A (which is May) needs to get appeared.
    I understand this part but the mathematical logic behind this is what I'm not very clear about.

  9. #9
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    13,804
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    3 Thread(s)

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

    Quote Originally Posted by Fazza View Post
    I'm guessing not relevant here because it seems a formula is preferred - quite a contrast, a formula that isn't understood and the simple SQL.

    SELECT A.[Column A] & B.[Column B]
    FROM A, B

    I tried and it worked like a charm.

    Very nice solution!

    M.

  10. #10
    New Member
    Join Date
    Aug 2012
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

      
    Quote Originally Posted by tusharm View Post
    That's something I've been doing very effectively for a long time. The idea is this. If you have 2 lists that you want to pick from so that you get every possible combination, you want to select 1 item from list A and combine it with every item in list B, then select the next item in list A and combine it with every item in list B.

    The way to loop through a list is to use a function that generates 1,2,3,...,N,1,2,3,...,N,... This is close to what the MOD function does. It generates values 0,1,2,...N-1,0,1,2,...,N-1,...

    So, ROW()-ROW(1st row) will generate 0,1,2,3,... and MOD(,N) will yield 0,1,2,...N-1,0,1,2,...,N-1,... Add 1 to that and you get the desired sequence.

    Now, for list A you want to increment the index only after every item in list B has been selected, which will happen every N rows. So, INT(ROW()-ROW(1st row) / N) will jump from one integer to the next every N rows.

    You can extend this concept to any number of lists A, B, C, D,...See
    Generate All Permutations
    Generate All Permutations

    Thanks a lot for your kind support... I'm new to this Forum & really looking forward for your help in future as well

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com