Formula to list all possible combinations of 4 rows in excel spreadsheet

[scampie]

I have a spreadsheet with 4 columns of data. I am trying to work out a formula to work out all possible combinations of the 4 columns. Each column has a different amount of data. It is text data. I had a working formula but Ive somehow broken it I want the output to contain all 4 of the columns, so don't want the combinations which only include 1,2 or 3 of data sets.

The non working formula I currently have is:

F5
=IF(ROW()-ROW($F$5)+1<=COUNTA($A$5:$A$298)*COUNTA(D$5:D$298)*COUNTA($C$5:$C$298)*COUNTA($D$5:$D$298), INDEX($A$5:$A$298,INT((ROW()-ROW($F$5))/(COUNTA($B$5:$B$298)*COUNTA($C$5:$C$298)*COUNTA($D$5:$D$298))+1)), "")

G5
=IF(ROW()-ROW($F$5)+1<=COUNTA($A$5:$A$298)*COUNTA($B$5:$B$298)*COUNTA($C$5:$C$298)*COUNTA($D$5:$D$298), INDEX($B$5:$B$298,MOD(ROW()-ROW($F$5),COUNTA($B$5:$B$298))+1), "")

H5
=IF(ROW()-ROW($F$5)+1<=COUNTA($A$5:$A$298)*COUNTA($B$5:$B$298)*COUNTA($C$5:$C$298)*COUNTA($D$5:$D$298), INDEX($C$5:$C$298,MOD(ROW()-ROW($F$5),COUNTA($C$5:$C$298))+1), "")

I5
=IF(ROW()-ROW($F$5)+1<=COUNTA($A$5:$A$298)*COUNTA($B$5:$B$298)*COUNTA($C$5:$C$298)*COUNTA($D$5:$D$298), INDEX($D$5:$D$298,MOD(ROW()-ROW($F$5),COUNTA($D$5:$D$298))+1), "")

Help a girl out!

Thanks in advance x

 

[shg]

There a workbook at https://app.box.com/s/47b28f19d794b25511be with both formula- and VBA-based methods to do that.

 

[scampie]

There a workbook at https://app.box.com/s/47b28f19d794b25511be with both formula- and VBA-based methods to do that.

Thats great thanks. The only issue is it repeats if you continue to drag the cells down.

 

[Eric W]

Consider post 4 here:

https://www.mrexcel.com/forum/excel...ed-combinations-formula-possible-outfits.html