Need a combinations formula for possible outfits

gravanoc

Active Member
Joined
Oct 20, 2015
Messages
346
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I need an excel spreadsheet that shows all the available combinations for a wardrobe. I think I have the logic right, but need a confirmation if this is right.

List of wardrobe items:
wwgpNbv.jpg


Logic for formula. Not sure the is correctx = 7 topsy = 6 Bottomsz = 5 Jacketsw = 4 Shoesv = 2 T-Shirtsn = # of outfits((x*v*y)+(v*y))*w*(z+1) = n((7*2*6)+(2*6))*4*(5+1) = n(84+12)*24=n2304 outfits

https://drive.google.com/open?id=1Sl5oSELRR1SdAipn1qR1xRLOwDZtsZma
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Also, I'm trying to make the full list of all the combinations using a formula. I have found a good formula that works with two columns, and am trying to update it to work with five columns.
=IF(ROW()-ROW($F$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($F$1))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1))
 
Upvote 0
I have tried modifying another formula: =IFERROR(INDEX($A:$A,IF(INT((ROW(1:1)-1)/((COUNTA(B:B)-1)*((COUNTA(C:C)-1)*((COUNTA(D:D)-1))*((COUNTA(E:E))))))+3>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)))))+2))&" "&INDEX(B:B,MOD(INT((ROW(1:1)-1)/(COUNTA(D:D)-1)),(COUNTA(C:C)-1))+2)&" "&INDEX(C:C,MOD((ROW(1:1)-1),(COUNTA(D:D)-1))+2)&" "&INDEX(D:D,MOD((ROW(1:1)-1),(COUNTA(E:E)-1))+2)&" "&INDEX(E:E,MOD((ROW(1:1)-1),(COUNTA(E:E)-1))+2),"")

It seems to be closer to what I need, but it's still missing something. It only extends to row 900, while the math seems to indicate it should go to 2048.
 
Upvote 0
I'm not sure where you got your formula to figure out how many combinations there are. It should just be the product of the number of items in each column, 1680 in this case. Consider this set-up:


Book1
ABCDEFGHIJKL
1
2x.topspantscoatshoest-shirt
3x12. Zip-up hoodie (black)joggersjeanluxblack12. Zip-up hoodie (black)joggersjeanluxblack
413 Hoodie (black)jeansovercoatcaswhite13 Hoodie (black)joggersjeanluxblack
514. Sweatshirtwool dressblazerleather14. Sweatshirtjoggersjeanluxblack
6Merino sweatercasual drawtrenchderbysMerino sweaterjoggersjeanluxblack
7poplinchinoquiltedpoplinjoggersjeanluxblack
8denimsweatdenimjoggersjeanluxblack
9flannelflanneljoggersjeanluxblack
1012. Zip-up hoodie (black)jeansjeanluxblack
1113 Hoodie (black)jeansjeanluxblack
1214. Sweatshirtjeansjeanluxblack
Sheet4
Cell Formulas
RangeFormula
H3{=IF(ROW()>PRODUCT(SUBTOTAL(3,OFFSET($B$3:$B$20,0,COLUMN($B:$F)-COLUMN($B:$B))))+ROW($B$3)-1,"",INDEX(B:B,MOD(INT((ROW(H3)-ROW(H$3))/PRODUCT(SUBTOTAL(3,OFFSET($A$3:$A$20,0,COLUMN($H:H)-COLUMN($H:$H))))),COUNTA(B$3:B$20))+ROW(B$3)))}
Press CTRL+SHIFT+ENTER to enter array formulas.


It should match your sample sheet (except I got tired of typing in your data - in the future, consider using a machine readable tool such as the HTML Maker in my signature). To make the formula work, make sure there is one value in A3:A20 - I used the x in A3. Then put the formula in H3, and confirm with Control+Shift+Enter. Copy to I3:L3, then copy H3:L3 down the columns as far as necessary. This should give you your list.
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top