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!

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

Glad to help!

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

Originally Posted by tusharm
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.

8. ## Similar formula slightly different data

Hi, can i please get some help. I am trying to work out this formula, similar to the one i used earlier but now altered. In cell A2 I have concatenated data from cells A5 and B5. I did this because I have a mix of numbers and text. That formula lives in cell A2. In the category cell B4, that is a drop-down list with multiple categories, the same goes with cell A5, those will have multiple employees. Basically, what I would like to do is this, if I go to drop-down A5 and select a name, then go to category B5 and select anything in this case delivery on cell C5 give me the dates that are associated with that data. I know it needs to reference a second spreadsheet which will look like the chart next to this one.

Spreadsheet X
 A1 B1 C1 D1 A2 Concatenated Data A5, B5 B2 C2 A3 B3 C3 A4 EMPLOYEE B4 CATEGORY C4 DATES A5 204Smith B5 Delivery C5
Spreadsheet X, tab XYZ
 Employee Delivery Pick-Up Purchase 204Smith 1/12/18 2/10/18

I would like something like this, I select 204Smith from the A5 drop-down menu and then Delivery from B5, therefore being referenced in cell A2 since it's concatenated and it should go into the same spreadsheet but tab xyz and go to cell A2 and then column and cell B2 and give me the date of 1/12/18 from that cell. I want it to do this everytime I changed the category. So, in other words, if I select purchase instead of delivery i want it to give the date 2/10/18 on cell C5.

## 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
•