Forum Discussion
Solved
Combinations/Permutations-How do I get all possible 2-way, 3-way, 4-way, 5-way combinations?
Please see attached file.
1st tab has Xs next to MH, SU, ED, etc.
The second sheet, I want to be able to pull in all UNIQUE possible combinations. So, for example: MH, MHDV, MHDVSA, etc.
Ultimately, would like the following combination of lists to be exhaustive:
2-way combination example: MHDV, MHSU, MHED
3-way combinatione example: MHDVSU, MHDVED, MHDVAD
4-way combination example: MHDVSUAD, DVSUADED, etc.
Hello Leila!
Here is how I would go about solving this problem. There might be better ways.
In this case, I like to employ a sort of helper column on your first sheet. You can even create this column and hide it if you are cognizant of how it looks. The helper column will exist right after the columns where you will "X" certain categories. It will be a new column "S" with the formula
=IF(N3="X",$N$2,"")&IF(O3="X",$O$2,"")&IF(P3="X",$P$2,"")&IF(Q3="X",$Q$2,"")&IF(R3="X",$R$2,"")
Basically, it will look at the "X"s to the left and create a code (MH, MHDV, MHDVSA, etc...) based on what was selected. Then, on the second sheet, it is easy to do a COUNTIF like you had done, simply comparing the combination in column A with the new column "S" on the first sheet.
I repeated it again for the second set of "X" columns, by creating a new column "AE" with the formula
=IF(Z3="X",$Z$2,"")&IF(AA3="X",$AA$2,"")&IF(AB3="X",$AB$2,"")&IF(AC3="X",$AC$2,"")&IF(AD3="X",$AD$2,"")
Then doing a COUNTIF on the second sheet as well. I've attached my version of your sheet here to look at.
As a sidenote, some of the column headers had spaces after the two-letter combinations that I had to remove in order for this to work. They were cells N2 and Z2 and a few others on sheet one.
As a variant, if without additional cells, first counting could be like
=SUMPRODUCT( ( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE("MHDVSUADED", 9,ISBLANK(OFFSET('SITE TRACKING'!$N$3,0,4,COUNTA('SITE TRACKING'!L:L)))*2,""), 7,ISBLANK(OFFSET('SITE TRACKING'!$N$3,0,3,COUNTA('SITE TRACKING'!L:L)))*2,""), 5,ISBLANK(OFFSET('SITE TRACKING'!$N$3,0,2,COUNTA('SITE TRACKING'!L:L)))*2,""), 3,ISBLANK(OFFSET('SITE TRACKING'!$N$3,0,1,COUNTA('SITE TRACKING'!L:L)))*2,""), 1,ISBLANK(OFFSET('SITE TRACKING'!$N$3,0,0,COUNTA('SITE TRACKING'!L:L)))*2,"") =A3)*1 )and exactly the same for the second one
=SUMPRODUCT( ( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE("MHDVSUADED", 9,ISBLANK(OFFSET('SITE TRACKING'!$Y$3,0,4,COUNTA('SITE TRACKING'!X:X)))*2,""), 7,ISBLANK(OFFSET('SITE TRACKING'!$Y$3,0,3,COUNTA('SITE TRACKING'!X:X)))*2,""), 5,ISBLANK(OFFSET('SITE TRACKING'!$Y$3,0,2,COUNTA('SITE TRACKING'!X:X)))*2,""), 3,ISBLANK(OFFSET('SITE TRACKING'!$Y$3,0,1,COUNTA('SITE TRACKING'!X:X)))*2,""), 1,ISBLANK(OFFSET('SITE TRACKING'!$Y$3,0,0,COUNTA('SITE TRACKING'!X:X)))*2,"") =A3)*1 )That's in attached file
Hello Leila!
Here is how I would go about solving this problem. There might be better ways.
In this case, I like to employ a sort of helper column on your first sheet. You can even create this column and hide it if you are cognizant of how it looks. The helper column will exist right after the columns where you will "X" certain categories. It will be a new column "S" with the formula
=IF(N3="X",$N$2,"")&IF(O3="X",$O$2,"")&IF(P3="X",$P$2,"")&IF(Q3="X",$Q$2,"")&IF(R3="X",$R$2,"")
Basically, it will look at the "X"s to the left and create a code (MH, MHDV, MHDVSA, etc...) based on what was selected. Then, on the second sheet, it is easy to do a COUNTIF like you had done, simply comparing the combination in column A with the new column "S" on the first sheet.
I repeated it again for the second set of "X" columns, by creating a new column "AE" with the formula
=IF(Z3="X",$Z$2,"")&IF(AA3="X",$AA$2,"")&IF(AB3="X",$AB$2,"")&IF(AC3="X",$AC$2,"")&IF(AD3="X",$AD$2,"")
Then doing a COUNTIF on the second sheet as well. I've attached my version of your sheet here to look at.
As a sidenote, some of the column headers had spaces after the two-letter combinations that I had to remove in order for this to work. They were cells N2 and Z2 and a few others on sheet one.
Thanks Bryant! I am going to take a look and try it out.
I appreciate it!
