How to list all possible combinations of the value

问题:

There is a question and answer already out there, "How to list all possible combinations of the values in three columns in excel?" This formula works exactly how I want it to, but I need added two additional columns, but I am not able to fully understand the current formula to add an additional two new columns to the list.

Current Formula works for 3 columns. It needs to be updated to include 5. =IFERROR(INDEX($A:$A,IF(INT((ROW(1:1)-1)/(((COUNTA(B:B)-1)*((COUNTA(C:C)-1)))))+2>COUNTA(A:A),-1,INT((ROW(1:1)-1)/(((COUNTA(B:B)-1)*((COUNTA(C:C)-1)))))+2))&" "&INDEX(B:B,MOD(INT((ROW(1:1)-1)/(COUNTA(C:C)-1)),(COUNTA(B:B)-1))+2)&" "&INDEX(C:C,MOD((ROW(1:1)-1),(COUNTA(C:C)-1))+2),"")

Also if there is a way to explain how to add for an additional or subtract a column that would be exponentially beneficial as well.

Site    Product Type    Labor Hours Machine Hours   Batch Size      
MAR UV  2   2   100     MAR UV 2
BEL SOLVENT 5   5   300     MAR UV 5
    WATER   8   8   750     MAR UV 8
        13  13  1750        MAR UV 13
        18  18  3750        MAR UV 18
                5000        MAR SOLVENT 2
                        MAR SOLVENT 5
                        MAR SOLVENT 8
                        MAR SOLVENT 13
                        MAR SOLVENT 18
                        MAR WATER 2
                        MAR WATER 5
                        MAR WATER 8
                        MAR WATER 13
                        MAR WATER 18
                        BEL UV 2
                        BEL UV 5
                        BEL UV 8
                        BEL UV 13
                        BEL UV 18
                        BEL SOLVENT 2
                        BEL SOLVENT 5
                        BEL SOLVENT 8
                        BEL SOLVENT 13
                        BEL SOLVENT 18
                        BEL WATER 2
                        BEL WATER 5
                        BEL WATER 8
                        BEL WATER 13
                        BEL WATER 18

This is what I am seeing right now based on the current formula. It is only including the first 3 columns. I need it to include the next 2 as well. I also like this formula because it doesn't care how many additional rows will be in each column which may change dramatically in the future.

Below is the original question that has only 3 columns in the formulas How to list all possible combinations of the values in three columns in excel?

回答1:

Here's a way to do it without a formula:

1. Create a Pivot Table for your columns.
2. Set your columns, in order, in the "Rows" field of the PivotTable.
3. Change your Layout to "Tabular Form", and "Repeat Item Labels"
4. Remove all Totals and Subtotals
5. In your filters, untick (blank)
6. Change your Fields to have the following settings:
   • Include new items in manual filter
   • Show items with no data

This will automatically give you all items. If you add items to the list, just right-click on your PivotTable and Refresh.

回答2:

I'm answering this part only :

a way to explain how to add for an additional or subtract a column

According to your first 3 columns "Site-Product-Type" there is "2-3-5" items in each columm. separating the original formula into 3 lines :

=IFERROR( INDEX($A:$A,IF(INT((ROW(1:1)-1)/(((COUNTA(B:B)-1)*((COUNTA(C:C)-1)))))+2>COUNTA(A:A),-1,INT((ROW(1:1)-1)/(((COUNTA(B:B)-1)*((COUNTA(C:C)-1)))))+2))

&" "& INDEX(B:B,MOD(INT((ROW(1:1)-1)/(COUNTA(C:C)-1)),(COUNTA(B:B)-1))+2)

&" "& INDEX(C:C,MOD((ROW(1:1)-1),(COUNTA(C:C)-1))+2),"") 

So each "Site" item need to be repeated 3 * 5 times (15 times - Eg. MAR & BEL). [line 1]

And for each "Site" item, each "Product" item need to be repeated 5 times ( Eg. UV, SOLVENT, WATER ) [line 2]

And for each "Product" item, each "Type" item need to be repeated 1 time ( Eg. 2,5,8,13,18 ) [line 3]

So the total number of output = 2*3*5 = 30 . [ this part was executed by iferror(... , "") of the formulae (no output after 30 ( or 3*2*5) lines) ]

In the cited formula.. it was done by relating the row number (as a counter, using row() ), counta() (to count the number of elements in each column, mod() (to get the repetition), and index() (to call each column item, depending on the row number processed - more info : last formulae in this link ).

---

Taking it to 5 columns, "Site-Product-Type-Labor-Hours" :

Get the number of elements/items for each column. (You should get 2-3-5-5-6 )

So the total number of output = 2*3*5*5*6 = 900 .

each "Site" item need to be repeated 3*5*5*6 times

for each "Site" item, each "Product" item need to be repeated 5*5*6 times

for each "Product" item, each "Type" item need to be repeated 5*6 times

for each "Type" item, each "Labor" item need to be repeated 6 times

for each "Labor" item, each "Hours" item need to be repeated 1 time

---

If you remove a column.. just use the same pattern.

I hope you get the logic. ( :