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

Page 6 of 6 FirstFirst ... 456
Results 51 to 57 of 57

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

  1. #51
    New Member
    Join Date
    Jul 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

     
    Thank you. This works great. I tried applying it to another worksheet and I keep getting a blank versus an answer. The formula should work identical to the info I sent you. I guess I need to figure out why there is an error. I don't know if has anything to do with the dropdown lists for the dates. I will post once I figure this out.

  2. #52
    New Member
    Join Date
    Jul 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi, I figured out what was causing my error. So now I have a new problem, if you look at the table on 2100400, as I mentioned this is a dropdown list of account numbers. Except, I forgot to mention that in that list, there will be one that says All. In order to display All accounts. So, basically your formula works great, it gives me the actual amount from the last column in the table below, for any given account from the dropdown list (see 2100400 below) in any given date range that I choose. However, when I select All from the dropdown of accounts, I get a blank. This is because what it does is, it looks at the cell below that says Transfer In210040042186 and it is now seeing Transfer InAll because when I change 2100400 on the dropdown to all it is concatenated, therefore, it'll change from Transfer In2100400 to Transfer InAll. Since Transfer In2100400 is technically reaching into column A below to find something that says Transfer InAll. This doesn't make sense because it's not going to find that. I want it to display all the Transferred In money from all the accounts. How do I incorporate All into your formula: =IFERROR(INDEX($H$2:$H$13,SMALL(IF($B$18=$A$2:$A$13,IF($D$2:$D$13>=$B$19,IF($D$2:$D$13<=$C$19,ROW($A$2:$A$13)-ROW($A$2)+1,""))),ROWS($B$18:B18))),"")
    ?
    Concatenate Category Date Date Concatenate Date Acct. No. Actual Amt.
    Operations100258042184 Operations 6/29/15 6/29/15 4218442184 1002580 0.18
    Transfer In210040042186 Transfer In 7/1/15 7/1/15 4218642186 2100400 0.03
    Capital310958642187 Capital 7/3/15 7/3/15 4218742187 3109586 0.24



    Transfer In Transfer In210040042186 0.03
    2100400 7/1/15 (42186 above) 7/13/15


  3. #53
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    5,632
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    0 Thread(s)

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

    Try:

    =IFERROR(INDEX($H$2:$H$13,SMALL(IF(($B$18=$A$2:$A$13)+($A$19="all"),IF($D$2:$D$13>=$B$19,IF($D$2:$D$13<=$C$19,ROW($A$2:$A$13)-ROW($A$2)+1,""))),ROWS($B$18:B18))),"")
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  4. #54
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    5,632
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    0 Thread(s)

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

    Come to think of it:

    =IFERROR(INDEX($H$2:$H$13,SMALL(IF(($B$18=$A$2:$A$13)+(($A$19="all")*($C$2:$C$13=$A$18)),IF($D$2:$D$13>=$B$19,IF($D$2:$D$13<=$C$19,ROW($A$2:$A$13)-ROW($A$2)+1,""))),ROWS($B$18:B18))),"")

    The last post will pick anything in the date range, this one will pick anything in the date range matching the category.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  5. #55
    New Member
    Join Date
    Jul 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Eric,

    I just wanted to say THANK YOU. This worked like a charm. You are awesome!

  6. #56
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    5,632
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    0 Thread(s)

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

    Glad to help!

  7. #57
    New Member
    Join Date
    Dec 2017
    Posts
    1
    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
    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.

    Excellent sir. What will be the modification in the formula when we have values in same row in two different table? The number of entries are also not constant, for example Alphabets in table 1 and numbers in Table 2

    Table 1 Table 2
    ColA ColB ColC ColD ColE
    AA BB 1 2 3
    AC BD 5 6
    AA 6

    Please help me to sort this problem. I am trying to modify your formula, but not much success.

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