0

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?

Adam
  • 1
  • 2
  • Have you considered a VBA solution? – SJR Jun 11 '19 at 18:55
  • I haven't unfortunately I have zero experience in Macros. Plus I have found a formula that works, just need to updated it to include additional columns. Any idea how I am able to add a person who answered the question for 3 columns? I'm sure he would be able to update it to five columns? – Adam Jun 11 '19 at 18:57
  • https://stackoverflow.com/questions/48651400/how-to-list-all-possible-combinations-of-the-values-in-three-columns-in-excel – Adam Jun 11 '19 at 19:04

2 Answers2

0

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 column. 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 = 235 = 30 . [ this part was executed by iferror(... , "") of the formulae (no output after 30 ( or 325) 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 = 23556 = 900 .

each "Site" item need to be repeated 355*6 times

for each "Site" item, each "Product" item need to be repeated 556 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. ( :

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
p._phidot_
  • 1,913
  • 1
  • 9
  • 17
  • Yup so I already understand how to figure out how many answers there are, the problem is I want to see all the answers. – Adam Jun 12 '19 at 12:05
  • yup, so you need a way to 'call' each element at every row, which what the [index](https://exceljet.net/excel-functions/excel-index-function) & modulus (`MOD()`) did.. | If you are going to add/remove column constantly, my recommendation is to rebuilt the formula. or do it stage by stage (using extra columns). – p._phidot_ Jun 13 '19 at 07:16
  • You can share the formula/sample file+formula that you had tried.. and we may assist from there... ( : – p._phidot_ Jun 13 '19 at 07:50
  • glad that you had "understand how to figure out how many answers there are".. my main concern is.. to figure out how the "3*5*5*6 times .. 5*5*6 times.. 5*6 times.. " part of algorithm is implemented inside the formula. || IMHO, the formulae you cited is easy to use.. but not easy to edit. That's why I recommend rebuilding it (once you get the pattern). – p._phidot_ Jun 14 '19 at 01:11
0

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.

Chronocidal
  • 6,827
  • 1
  • 12
  • 26
  • I must be doing something incorrect because all I'm seeing is the same exact information I started with, but its not showing me the possible combinations. If I put all my information in the Rows section, how would that provide all "ordered" combinations or permutations. – Adam Jun 12 '19 at 12:11
  • @Adam Have you turned on "Show items with no data" for all of the fields? (you need to do each field individually) – Chronocidal Jun 12 '19 at 12:51
  • Thank you @Chronocidal this worked! I believe this will be even better than the excel since the customer can add additional rows are columns and the pivot will update. – Adam Jun 17 '19 at 17:59
  • The instructions are unclear. What exactly should I click? – root May 21 '22 at 22:56