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

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

1. ## 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. ## 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. ## 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))),"")

4. ## 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.

5. ## 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!

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

Originally Posted by tusharm

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

## User Tag List

#### Posting Permissions

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