CODEWITHSUNDEEP
excelvba
Snowy
Snowy

Reputation: 6132

Excel Row Combinations?

I have 4 available "slots". I have 4 "objects", and need to create combinations of objects to each one gets placed in a "slot" in a rotation. So for example, if I have these objects:

val1
val2
val3
val4

I want to use script/macro/etc to make combinations of the range of values, to make this:

val1
val2
val3
val4

val2
val3
val4
val1

val3
val4
val1
val2

val4
val1
val2
val3

Next, I have 6 "objects", and each of them needs a placement in every slot, so there will always be a combination where 2 objects are not included, but after all combinations are done, each object visits one slot once.

I am not sure what web-search keywords to use. I am a bit stuck. What is the best way to do that? I have Excel 2013 installed.

Thanks.

EDIT.

Based on input from Joe, I now have the following code:

Public Sub makeMore()
  Dim looper As Integer, colloop As Integer, numcols As Integer
  numcols = Cells(1, 1).Value
  For colloop = 1 To numcols
    For looper = Selection.Row To ((Selection.Row + Selection.Count) - 1)
      ActiveSheet.Cells(looper, colloop).Value = ActiveSheet.Cells((looper + colloop - 2) Mod numcols + 1, 1).Value
    Next looper
   Next colloop
End Sub

I put the value "6" from A1 on the sheet, put values from C4 to C9 "val1" "val2" "val3" "val4" "val5" "val6", selected the values, and what I see is the number "6" spread across the sheet. What I am trying to produce is multiple sets of 4, with each "val" being equally represented across the entirety of all sets. I am missing something in the value setting in the loop, I think.

All further ideas appreciated. Thanks again.

Upvotes: 2

Views: 225

Answers (2)

Joe
Joe

Reputation: 63424

This would do it; I have it read from O15 the value of # of rows. You can either set that in a constant at the top of the program, read it in from somewhere, put it in a msgbox, whatever you want.

Public Sub makeMore()
  Dim looper As Integer, colloop As Integer, numcols As Integer
  numcols = Cells(15, 15).Value
  For colloop = 2 To numcols
    For looper = 1 To 4
      ActiveSheet.Cells(looper, colloop).Value = ActiveSheet.Cells((looper + colloop - 2) Mod numcols + 1, 1).Value
    Next looper
   Next colloop

End Sub

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53126

Pseudo code solution

Get reference to the data range
Copy range data to a variant array
Get reference to output range
for i = 1 to number of output sets
    write array to sheet
    rotate array data
    offset output range reference
next i

Have a go at coding this, and post back if you get stuck

Upvotes: 0

Related Questions