How to Display All Combinations for Several Columns

[gordonhtfu]

I have this problem that I am trying to resolve with Excel but I couldn't think of a solution. Does anyone know of a solution?

Here is a simplified problem:
Assume that you have two columns ...
(Column1) (Column2)
Dogs Cats
Tom Marie
Peter Lucy
John

How do you make it so that it would display in this format?
Dogs Tom
Dogs Peter
Dogs John
Cats Marie
Cats Lucy

By the way, the actual problem that I have could have up to 100 columns so a solution that can be easily expanded would be really good.

 

[XOR LX]

Hi,

Your example does not include any blanks between the first and last entries in each of your columns, so I will assume that this will never be the case.

Go to Name Manager and create a new name, Range1 say, and define it as that range which forms the smallest rectangle which encompasses all of your data in question (for example, with your data above this might be A1:B4).

Exit Name Manager. Then, for your first column, enter this array formula** in the first cell:

=IFERROR(INDEX(Range1,1,MATCH(TRUE,MMULT((--(TRANSPOSE(COLUMN(Range1))>=COLUMN(Range1))),SUBTOTAL(3,OFFSET(INDEX(Range1,,1),1,ROW(INDIRECT("1:"&COLUMNS(Range1)))-1,,)))>=ROWS($1:1),0)),"")

Drag down until you start to get blanks for the results.

In the adjacent column, enter this (normal) formula in the first cell:

=IFERROR(INDEX(INDEX(Range1,,MATCH(F1,INDEX(Range1,1,),0)),1+COUNTIF($F$1:$F1,F1),1),"")

Copy down as required.

Regards</SPAN>

 

[XOR LX]

Apologies. I forgot to mention that the first formula I gave you should be placed in cell F1 in order for the second formula to work. If you wish to place it elsewhere, no problem: simply amend all references to that cell in the second formula accordingly.

Regards

 

[shg]

There's a workbook that will do this at https://app.box.com/s/47b28f19d794b25511be

 

[gordonhtfu]

Apologies. I forgot to mention that the first formula I gave you should be placed in cell F1 in order for the second formula to work. If you wish to place it elsewhere, no problem: simply amend all references to that cell in the second formula accordingly.

Regards

Thanks XOR LX! That works well on Excel 2010. Do you know what would be the equivalent for Excel 2003? It is giving me an error. I think IFERROR and COLUMNS are not supported on Excel 2003.

Thanks shg too! I think the example you provided uses a Macro right? I am basic familiarity with it and I would try to use the sample macro and tailor it to my need.

 

[shg]

The workbook has both VBA- and formula-based examples. To use the formulas, you need not enable macros.

 

[XOR LX]

For 2003, perhaps change the formula (still array) in F1 to:

=IF(ROWS($1:1)>COUNTA(Range1)-COLUMNS(Range1),"",INDEX(Range1,1,MATCH(TRUE,MMULT((--(TRANSPOSE(COLUMN(Range1))>=COLUMN(Range1))),SUBTOTAL(3,OFFSET(INDEX(Range1,,1),1,ROW(INDIRECT("1:"&COLUMNS(Range1)))-1,,)))>=ROWS($1:1),0)))

And that in G1 to:

=IF(F1="","",INDEX(INDEX(Range1,,MATCH(F1,INDEX(Range1,1,),0)),1+COUNTIF($F$1:$F1,F1),1))

Regards

 

[gordonhtfu]

For 2003, perhaps change the formula (still array) in F1 to:

=IF(ROWS($1:1)>COUNTA(Range1)-COLUMNS(Range1),"",INDEX(Range1,1,MATCH(TRUE,MMULT((--(TRANSPOSE(COLUMN(Range1))>=COLUMN(Range1))),SUBTOTAL(3,OFFSET(INDEX(Range1,,1),1,ROW(INDIRECT("1:"&COLUMNS(Range1)))-1,,)))>=ROWS($1:1),0)))

And that in G1 to:

=IF(F1="","",INDEX(INDEX(Range1,,MATCH(F1,INDEX(Range1,1,),0)),1+COUNTIF($F$1:$F1,F1),1))

Regards

Really appreciate your help XOR LX. That works on Excel 2010 but still giving me an error on Excel 2003. Excel 2003 is highlighting COLUMNS of the ref_text part of the INDIRECT(ref_text, [a1]) formula, as the source of error.

 

[XOR LX]

Ah yes. It's not the formulas, but the nesting limit in 2003.

Create a second Defined Name, Row_Count, and enter this in the Refers to: box:

=ROW(INDIRECT("1:"&COLUMNS(Range1)))

Your formula in F1 then becomes:

=IF(ROWS($1:1)>COUNTA(Range1)-COLUMNS(Range1),"",INDEX(Range1,1,MATCH(TRUE,MMULT((--(TRANSPOSE(COLUMN(Range1))>=COLUMN(Range1))),SUBTOTAL(3,OFFSET(INDEX(Range1,,1),1,Row_Count-1,,)))>=ROWS($1:1),0)))

and should comply with this nesting limit now.

Regards

 

[gordonhtfu]

Ah yes. It's not the formulas, but the nesting limit in 2003.

Create a second Defined Name, Row_Count, and enter this in the Refers to: box:

=ROW(INDIRECT("1:"&COLUMNS(Range1)))

Your formula in F1 then becomes:

=IF(ROWS($1:1)>COUNTA(Range1)-COLUMNS(Range1),"",INDEX(Range1,1,MATCH(TRUE,MMULT((--(TRANSPOSE(COLUMN(Range1))>=COLUMN(Range1))),SUBTOTAL(3,OFFSET(INDEX(Range1,,1),1,Row_Count-1,,)))>=ROWS($1:1),0)))

and should comply with this nesting limit now.

Regards

Fantastic Stuff. XOR LX, you are my lifesaver...

I am trying to do it for the other scenario where the values are transposed from columns to rows and vice versa.

Dogs</SPAN>	Tom</SPAN>	Peter</SPAN>	John</SPAN>
Cats</SPAN>	Marie</SPAN>	Lucy</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=3></COLGROUP>

It's not working for me... do you know what should be the solution?
Column_Count -> =COLUMN(INDIRECT("1:"&ROWS(Range1)))
Range1 -> =Sheet1!$A$1:$D$2
A6 (array formula)-> =IF(COLUMNS($A:A)>COUNTA(Range1)-ROWS(Range1),"",INDEX(Range1,1,MATCH(TRUE,MMULT((--(TRANSPOSE(ROW(Range1))>=ROW(Range1))),SUBTOTAL(3,OFFSET(INDEX(Range1,,1),1,Column_Count-1,,)))>=COLUMNS($A:A),0)))
A7 -> =IF(A6="","",INDEX(INDEX(Range1,,MATCH(A6,INDEX(Range1,1,),0)),1+COUNTIF($A$6:$A6,A6),1))