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]

You mean I misinterpreted your original question?!

Or that was correct but now you also want to do the converse? If so, could you provide a slightly clearer example than the one you've given, preferably with more than just a handful of entries?

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

You mean I misinterpreted your original question?!

Or that was correct but now you also want to do the converse? If so, could you provide a slightly clearer example than the one you've given, preferably with more than just a handful of entries?

Regards

You interpreted the original question correctly and now I am looking for the converse.

Given:

Dogs	Tom	Peter	John
Cats	Marie	Lucy

<tbody>
</tbody>

Looking to generate:

Dogs	Tom
Dogs	Peter
Dogs	John
Cats	Marie
Cats	Lucy

<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

 

[XOR LX]

For that way, the formula (still array) in F1 should be:

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

And that in G1:

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

again, where Range1 is that range which comprises the smallest rectangle which encloses your data set.

Regards

 

[gordonhtfu]

Thanks XOR LX.

I have another variation of the original where I think the formula need to be adjusted... I spent like a few hours trial and errors and couldn't find the solution.

Here is the actual problem I am trying to resolve with formula with Excel 2003 (I think 2003 had the nesting limit limitation):

Criteria:
There are 13 columns. The values of the first 2 columns do not matter. The value of the 3rd column is the heading. The value of 4th to 13th column are colors (They may be blank too).
There are indefinite number of rows (In the sample problem below, I only showed 6 rows but the solution should work for any number of rows).

Here is the sample problem:

Heading1

Heading2

Heading3

Color1

Color2

Color3

Color4

Color5

Color6

Color7

Color8

Color9

Color10

1

asdf

Heads

Blue

Green

Yellow

Red

Blue

2

asdf

Tail

Red

Green

Green

Blue

Red

Yellow

3

asdf

Shoulder

Yellow

Black

4

asdf

Leg

Blue

Green

Yellow

Red

Green

Yellow

Red

Green

5

sdf

Foot

Gold

6

df

Hands

Blue

Yellow

<tbody>
</tbody>

What to do?
Make a new table so that it will display in two columns (Heading + Color) with as many rows as appropriate ... all duplicates should be removed.
This is to be used for Excel 2003.

Here is the sample solution to the sample scenario above:

Heads	Blue
Heads	Green
Heads	Yellow
Heads	Red
Tail	Red
Tail	Green
Tail	Blue
Tail	Yellow
Shoulder	Yellow
Shoulder	Black
Leg	Blue
Leg	Green
Leg	Yellow
Leg	Red
Foot	Gold
Hands	Yellow

<tbody>
</tbody>

Reference:
http://www.mrexcel.com/forum/excel-q...-new-post.html
How to Display All Combinations for Several Columns

 

[XOR LX]

Hi,

The matter of returning unique items only complicates matters slightly.

Define Range1 this time as that range which comprises the smallest rectangle which encloses all the data underneath (but not including) Heading3 and across to your final Color (Color10 in your example).

In the cell immediately to the right of the top-rightmost cell in Range1, enter this array formula** and copy down as required:

=SUM(IF(INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),ROWS($1:1),)<>"",1/COUNTIF(INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),ROWS($1:1),),INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),ROWS($1:1),))))

Then, in e.g. Q1 (again, array-entered):

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

Copy down as required.

In R1 (again, array-entered)

=IF(Q1="","",INDEX(INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),MATCH(Q1,INDEX(Range1,,1),0),),1,SMALL(IF(FREQUENCY(IF(INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),MATCH(Q1,INDEX(Range1,,1),0),)<>0,MATCH(INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),MATCH(Q1,INDEX(Range1,,1),0),),INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),MATCH(Q1,INDEX(Range1,,1),0),),0)),COLUMN(Range1)-MIN(COLUMN(Range1))+1),TRANSPOSE(COLUMN(Range1)-MIN(COLUMN(Range1))+1)),COUNTIF($Q$1:$Q1,Q1))))

Copy down as required.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

[gordonhtfu]

Hi,

The matter of returning unique items only complicates matters slightly.

Define Range1 this time as that range which comprises the smallest rectangle which encloses all the data underneath (but not including) Heading3 and across to your final Color (Color10 in your example).

In the cell immediately to the right of the top-rightmost cell in Range1, enter this array formula** and copy down as required:

=SUM(IF(INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),ROWS($1:1),)<>"",1/COUNTIF(INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),ROWS($1:1),),INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),ROWS($1:1),))))

Then, in e.g. Q1 (again, array-entered):

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

Copy down as required.

In R1 (again, array-entered)

=IF(Q1="","",INDEX(INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),MATCH(Q1,INDEX(Range1,,1),0),),1,SMALL(IF(FREQUENCY(IF(INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),MATCH(Q1,INDEX(Range1,,1),0),)<>0,MATCH(INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),MATCH(Q1,INDEX(Range1,,1),0),),INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),MATCH(Q1,INDEX(Range1,,1),0),),0)),COLUMN(Range1)-MIN(COLUMN(Range1))+1),TRANSPOSE(COLUMN(Range1)-MIN(COLUMN(Range1))+1)),COUNTIF($Q$1:$Q1,Q1))))

Copy down as required.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Hello XLOR,

Currently, the array formula for Q1 is working great (it is displaying the headings) but the one in R1 (which contains the colors) is giving me an error in Excel 2003 (Works fine on newer Excel though)

This is the array formula you gave me for R1:
=IF(Q1="","",INDEX(INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),MATCH(Q1,INDEX(Range1,,1),0),),1,SMALL(IF(FREQUENCY(IF(INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),MATCH(Q1,INDEX(Range1,,1),0),)<>0,MATCH(INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),MATCH(Q1,INDEX(Range1,,1),0),),INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),MATCH(Q1,INDEX(Range1,,1),0),),0)),COLUMN(Range1)-MIN(COLUMN(Range1))+1),TRANSPOSE(COLUMN(Range1)-MIN(COLUMN(Range1))+1)),COUNTIF($Q$1:$Q1,Q1))))

Currently, it is highlighting that the second COLUMNS in the equation is causing the error in Excel 2003.

 

[XOR LX]

Hmmm,

That 2003 nesting limitation again. I think it's best to create a couple of further Named Ranges to get around this. I also think the formula in Q1 should've errored in your case, so best to account for this one as well:

Name: Array1
Refers to: =ROW(INDIRECT("1:"&ROWS(Range1)))

Name: Array2
Refers to: =INDEX(OFFSET(Range1,,1,,COLUMNS(Range1)-1),MATCH(Sheet1!$Q1,INDEX(Range1,,1),0),)

Formulas are now:

In Q1:

=IF(ROWS($1:1)>SUM(OFFSET(Range1,,COLUMNS(Range1),,1)),"",INDEX(Range1,MATCH(TRUE,MMULT((--(ROW(Range1)>=TRANSPOSE(ROW(Range1)))),SUBTOTAL(9,OFFSET(INDEX(Range1,1,1),Array1-1,COLUMNS(Range1),,)))>=ROWS($1:1),0),1))

In R1:

=IF(Q1="","",INDEX(Array2,1,SMALL(IF(FREQUENCY(IF(Array2<>0,MATCH(Array2,Array2,0)),COLUMN(Range1)-MIN(COLUMN(Range1))+1),TRANSPOSE(COLUMN(Range1)-MIN(COLUMN(Range1))+1)),COUNTIF($Q$1:$Q1,Q1))))

and should now be compatible with the nesting limit in 2003.

Regards