0

I have a few rows (the number of rows should be dynamic) of data that I would like to combine, and get each unique combination. I am able to do that for a specific number of rows of data, but when the number of rows is not fixed, what should I do in that case?

For Example:

Input data:

Row 1 : Correct    Wrong    Blank
Row 2 : Correct1   Wrong1   Blank1
Row 3 : Ok         Cancel   

Output should be like:

Correct  Correct1   Ok
Correct  Correct1   Cancel
Correct  Wrong1     Ok
Correct  Wrong1     Cancel
Correct  Blank1     Ok
Correct  Blank1     Cancel
Wrong    Correct1   Ok
Wrong    Correct1   Cancel
Wrong    Wrong1     Ok
Wrong    Wrong1     Cancel
Wrong    Blank1     Ok
Wrong    Blank1     Cancel
Blank    Correct1   Ok
Blank    Correct1   Cancel
Blank    Wrong1     Ok
Blank    Wrong1     Cancel
Blank    Blank1     Ok
Blank    Blank1     Cancel
1
  • Welcome to Super User.  You're more likely to get help if you show us the solution that you already have, and describe what you've tried in your attempt to generalize it. Jun 7, 2015 at 11:05

1 Answer 1

0

The following VBA macro will read data from Sheet1 and put the results in Sheet2:

Sub Combinations()
Dim r1 As Range, r2 As Range, r3 As Range
Dim v1 As Variant, v2 As Variant, v3 As Variant
Dim rw1 As Range, rw2 As Range, rw3 As Range
Dim s As Worksheet, K As Long
Set s = Sheets("Sheet2")
K = 1
With Sheets("Sheet1")
   Set rw1 = .Rows(1).Cells.SpecialCells(xlCellTypeConstants)
   Set rw2 = .Rows(2).Cells.SpecialCells(xlCellTypeConstants)
   Set rw3 = .Rows(3).Cells.SpecialCells(xlCellTypeConstants)
End With

For Each r1 In rw1
   v1 = r1.Value
   For Each r2 In rw2
      v2 = r2.Value
      For Each r3 In rw3
         v3 = r3.Value
         s.Cells(K, 1) = v1
         s.Cells(K, 2) = v2
         s.Cells(K, 3) = v3
         K = K + 1
      Next r3
   Next r2
Next r1

End Sub
2
  • Hi Gary's Student thanks for the reply. But i am able to do it for 3 column of data but when the no of column or the data source is not fixed , what should i do in that case.
    – Shom
    Jun 24, 2015 at 14:50
  • @Shom Since the code is dependent upon the number of columns, you would need to modify it for each case. Jun 24, 2015 at 15:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .