I have this challenge.
I have six columns, each of which has different kinds of master data as shown below:
Now, I want to have all possible combinations of these six cells - like (and so on)
Im trying to take this with the formula shown below:
=IF.ERROR(INDEX($A:$A,IF(INT((ROW(1:1)-1)/(((COUNTA(B:B)-1)*(COUNTA(C:C)-1)*(COUNTA(D:D)-1)*(COUNTA(E:E)-1)*(COUNTA(F:F)-1))))+2>COUNTA(A:A),-1,INT((ROW(1:1)-1)/(((COUNTA(B:B)-1)*(COUNTA(C:C)-1)*(COUNTA(D:D)-1)*(COUNTA(E:E)-1)*(COUNTA(F:F)-1))))+2))&" "INDEX(B:B,MOD(INT((ROW(1:1)-1)/(COUNTA(C:C)-1)),(COUNTA(B:B)-1))+2)&" "INDEX(C:C,MOD(INT((ROW(1:1)-1)/(COUNTA(D:D)-1)),(COUNTA(C:C)-1))+2)&" "INDEX(D:D,MOD(INT((ROW(1:1)-1)/(COUNTA(E:E)-1)),(COUNTA(D:D)-1))+2)&" "INDEX(E:E,MOD(INT((ROW(1:1)-1)/(COUNTA(F:F)-1)),(COUNTA(E:E)-1))+2)&" "INDEX(F:F,MOD((ROW(1:1)-1),(COUNTA(F:F)-1))+2," ")
I'm applying to each position (a,b,c,d,e,f) to achieve the variation of each one in the last position as you will see in the next image.
My excel it is in Spanish, so the formula it's an adaptation of the formula shown in: How to list all possible combinations of the values in three columns in excel?
I don't know if I am doing well because this formula is giving me a lot of duplicates without sense and I'm not able to verify if this formula is covering ALL possible combinations.
I really appreciate any help with this.
