Need help with returning combinations - I am not sure what is wrong with my formula?

Hi Everyone,

Forgive me in advance for posting for what will be for many of you a basic question, so I thank you in advance for your help and understanding. I have a spreadsheet, that has 4 columns of numbers and I want to return the combinations available in a new column. I have inserted a screenshot showing an example of the data. I am trying to return every combination for these columns - there's 1728 combinations by my calculations. Each column of numbers can be found in columns A C E G of my excel spreadsheet.Here is my formula? Please help me by telling me what is wrong with it and how I can get it to work

=IFERROR(INDEX($A$1:$A$8,INT((ROW(1:1)-1)/((COUNTA(C:C)*(COUNTA(E:E)*(COUNTA(G:G))))))+1)&" - "&INDEX(C:C,MOD(INT((ROW(1:1)-1)/COUNTA(E:E))/COUNTA(G:G)),COUNTA(C:C))+1)&" - "&INDEX(E:E,MOD(INT((ROW(1:1)-1)/COUNTA(G:G)),COUNTA(E:E))+1)&" - "&INDEX(G:G,MOD((ROW(1:1)-1),COUNTA(G:G))+1),""))


Excel Image.PNG

I'd do it like this:

Formula:

Please Login or Register  to view this content.

next time, please attach a sample sheet, not a picture of one, and ensure thta the sheet and the posted formula match each other (your pic and formula did not).

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Silly me. I was happy when I got 1728 results and didn't check it thoroughly. The middle two required a MOD on the outside of the INT to get them to repeat properly.

I've left the intermediate columns in place on this sheet... just in case I have to go back to it again!!