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

[erangai]

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

 

[tusharm]

Lightly tested:
=IF(ROW()-ROW($F$1)+1>(COUNTA(A:A)-1)*(COUNTA(B:B)-1),"",INDEX(A:A,1+INT((ROW()-ROW($F$1))/(COUNTA(B:B)-1)+1))&INDEX(B:B,1+MOD(ROW()-ROW($F$1),COUNTA(B:B)-1)+1))

Hi - I found this AWESOME formula and still trying to fully understand it. Could you kindly share, how do I update it to omit the header row for column A and B? Thank you in advance!

 

[linathehyena]

Lightly tested:
=IF(ROW()-ROW($F$1)+1>(COUNTA(A:A)-1)*(COUNTA(B:B)-1),"",INDEX(A:A,1+INT((ROW()-ROW($F$1))/(COUNTA(B:B)-1)+1))&INDEX(B:B,1+MOD(ROW()-ROW($F$1),COUNTA(B:B)-1)+1))

This works like a dream! Thank you so much! Now I need to add one more condition and change the output, let's say you have data in 3 columns:
Column A stores numbers
Column B & C stores colors

IF number in column A is less than 8 digits (need to be able to adjust that in case I need to change the length) then generate all possible combination with this number and all colors from column B but if the number in column A is more than 8 digits then generate all possible combinations with this number and all colors from column C. I am also trying to output the information in 2 separate columns, one for number and one for color (example below).
I figured out how to split your original formula to output the data in 2 columns but I am at a complete loss how to integrate the latest condition.
If possible, can you please explain your steps. I would love to further my knowledge and you are a wizard ^_^

[TABLE="class: grid, width: 500, align: left"]
[TR]
[TD]A - NUMBER[/TD]
[TD]B - COLOR[/TD]
[TD]C - COLOR[/TD]
[TD]OUTPUT NUMBER[/TD]
[TD]OUTPUT COLOR[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]RED[/TD]
[TD]RED[/TD]
[TD]1[/TD]
[TD]RED[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BLUE[/TD]
[TD]BLUE[/TD]
[TD]1[/TD]
[TD]BLUE[/TD]
[/TR]
[TR]
[TD]10101010[/TD]
[TD]YELLOW[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]YELLOW[/TD]
[/TR]
[TR]
[TD]12121212[/TD]
[TD]GREEN[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]GREEN[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]RED[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]BLUE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]YELLOW[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]GREEN[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10101010[/TD]
[TD]RED[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10101010[/TD]
[TD]BLUE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12121212[/TD]
[TD]RED[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12121212[/TD]
[TD]BLUE[/TD]
[/TR]
[/TABLE]

 

[tusharm]

Separate your problem into 2 problems. One with the 1st 2 numbers in columnn A and the data in column B and the other with the last 2 numbers in column A and the data from column C. Now, you will have 2 problems, each with 2 columns that you know how to solve. At the end, integrate the results.

 

[linathehyena]

Separate your problem into 2 problems. One with the 1st 2 numbers in column A and the data in column B and the other with the last 2 numbers in column A and the data from column C. Now, you will have 2 problems, each with 2 columns that you know how to solve. At the end, integrate the results.

The list I posted was just an example, the list of numbers and colors is unknown and the numbers will NOT be sorted by length. I read through all the posts on this subject and was able to decipher your previous formula to understand which part takes care of which task but I am not familiar enough to determine how numbers should be mixed with which colors based on length.

 

[samyscraps01]

My problem is slightly different. I am trying to figure out how I can create possible combinations based on two dropdown lists that each contain date ranges. Ex. I have a date range dropdown list in cell B2 and a dropdown in cell C2. The user can choose the range as he likes. So, say I have 7/1/2015 in B2 and 7/13/2015 in C2 and I concatenate that gives me 4218642198. That's great, except that I need that to find that date in column D that has this formula....=IFERROR(INDEX($H$2:$H$13,SMALL(IF($C$21=$A$2:$A$13,ROW($A$2:$A$13)-MIN(ROW($A$2:$A$13))+1,""),ROW(A1))),"") this formula is based on column D and E (column D and E have the same exact date lists) which I concatenated to combine my date ranges. However, as you probably already guess 4218642198 will never appear here because column D and E are the same date ranges. How can I get a formula to give me all possible combinations of dates based on what the user selects on the dropdown lists? Is that even possible?

 

[Eric W]

I'm trying to picture what you want. Could you post some sample data, with expected results?

 

[samyscraps01]

[TABLE="class: cms_table_outer_border, width: 500, align: left"][TR]
[TD]Concatenate[/TD]
[TD]Category[/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[TD]Concatenate Date[/TD]
[TD]Acct. No.[/TD]
[TD]Actual Amt.[/TD]
[/TR]
[TR]
[TD]Operations100258042184[/TD]
[TD]Operations[/TD]
[TD]6/29/15[/TD]
[TD]6/29/15[/TD]
[TD]4218442184[/TD]
[TD]1002580[/TD]
[TD]0.18[/TD]
[/TR]
[TR]
[TD]Transfer In210040042186[/TD]
[TD]Transfer In[/TD]
[TD]7/1/15[/TD]
[TD]7/1/15[/TD]
[TD]4218642186[/TD]
[TD]2100400[/TD]
[TD]0.03[/TD]
[/TR]
[TR]
[TD]Capital310958642187[/TD]
[TD]Capital[/TD]
[TD]7/3/15[/TD]
[TD]7/3/15[/TD]
[TD]4218742187[/TD]
[TD]3109586[/TD]
[TD]0.24[/TD]
[/TR][/TABLE]









[TABLE="class: cms_table_outer_border, width: 500"]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Transfer In[/TD]
[TD]Transfer In210040042186[/TD]
[TD]0.03[/TD]
[/TR]
[TR]
[TD]2100400[/TD]
[TD]7/1/15 (42186 above)[/TD]
[TD]7/13/15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

I need to find the amount in column H (titled Actual) based on three types of criteria.


1. I found it easier to concatenate data on column A. That includes two of my criteria, which is Category, and the Acct. No. derived from columns C and G. More specifically in this case, I am looking at just "Transfer In" from the Category-C Column.


2. The third criteria is where it gets complicated. I have two drop down lists on B19 and C19 that contain date ranges. (The dropdown lists are disabled but you get the idea, the date ranges will fluctuate based on the user selection). The date ranges in each list are identical and are derived from a separate report not shown in this spreadsheet. This allows the user to choose the date range of their choice. The idea is that when I change cell A19 (it should be dropdown list with more account numbers but for now, I am just using one account number in that cell), it will change cell B18.


3. The purpose of doing is that I need to find what the actual amount of money was transferred in. I am using the account number, the category and of course the date to determine that. The problem is that when I use only one date, so, say the date in B19. I will get that exact date. If i'm a user I will only get a response in cell C22 if I happen to know the date that transaction of the transfer in occurred, or I select it at random. I don't want this, I want to know that if the user selected a date range and the transaction occurred within that range, it should display it on C22. I used the formula below and it works well but of course it's only looking at cell B18 and cell B18 only includes one date. Can anyone help with this? I was trying to upload a spreadsheet but for some reason this site makes it extremely difficult to upload anything. I couldn't even get a jpeg on here.


=IFERROR(INDEX($H$2:$H$13,SMALL(IF($B$18=$A$2:$A$13,ROW($A$2:$A$13)-MIN(ROW($A$2:$A$13))+1,""),ROW(A1))),"")


Thank you.

Samy

 

[Eric W]

Try this:

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

 

[samyscraps01]

It works great, except for one thing,

[TABLE="class: cms_table_cms_table_outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Concatenate[/TD]
[TD]Category[/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[TD]Concatenate Date[/TD]
[TD]Acct. No.[/TD]
[TD]Actual Amt.[/TD]
[/TR]
[TR]
[TD]Operations100258042184[/TD]
[TD]Operations[/TD]
[TD]6/29/15[/TD]
[TD]6/29/15[/TD]
[TD]4218442184[/TD]
[TD]1002580[/TD]
[TD]0.18[/TD]
[/TR]
[TR]
[TD]Transfer In210040042186[/TD]
[TD]Transfer In[/TD]
[TD]7/1/15[/TD]
[TD]7/1/15[/TD]
[TD]4218642186[/TD]
[TD]2100400[/TD]
[TD]0.03[/TD]
[/TR]
[TR]
[TD]Capital310958642187[/TD]
[TD]Capital[/TD]
[TD]7/3/15[/TD]
[TD]7/3/15[/TD]
[TD]4218742187[/TD]
[TD]3109586[/TD]
[TD]0.24[/TD]
[/TR]
</tbody>[/TABLE]











If you look at the table above, I only have one transfer in but in my data I will have more than one transfer in with the same account no. With the formula, i'm only getting one result even though, as an example, i'm using account number 2102650 above, I have a date range of 7/1/15-7/13/15. Therefore, with your formula I should have a result of 0.03 and I do, except that I also have another transfer that occurred on 7/13/15 (within the specified date range), with the same account number and I only get duplicates of the same answer when I drag the formula. Can it not display all the results when I drag the formula? In other words, picture the table above, if you change the last row to say Transfer In instead of capital under the category in the last row and the account no. to 2100400 and keep the 7/3/15 date, I should hypothetically be able to see two results, one for 0.03 and one for 0.24. Correct? I'm not seeing that, I see the results of 0.03 over and over.

 

[Eric W]

Do you plan to drag the formula down, or to the right?

If down:

=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))),"")

If to the right:

=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,""))),COLUMNS($B$18:B18))),"")