Insert Copied Repeated rows under each Row - can it be done using a tool or vba?

[firassali]

Hello,

i have a column as seen below

and another Column as seen below

I need each row from Column A to have all possible values from criteria 2 to show as follows:

Can we do it using an excel tool or VBA? since i will need to do this for 300 criteria 1 and 300 criteria 2

Thank you in advance all,

 

[DanteAmor]

Hi and welcome to MrExcel.

Your data in columns A and B, the results in columns D and E, as shown in the following minisheet:

Dante Amor
	A	B	C	D	E
1	Criteria 1	Cirteria 2		Criteria 1	Cirteria 2
2	A	1		A	1
3	B	2		A	2
4	C	3		A	3
5		4		A	4
6				B	1
7				B	2
8				B	3
9				B	4
10				C	1
11				C	2
12				C	3
13				C	4
Hoja2

Try this macro:

Sub CombineCriterias()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
 
  a = Range("A2", Range("A" & Rows.Count).End(3)).Value
  b = Range("B2", Range("B" & Rows.Count).End(3)).Value
  ReDim c(1 To UBound(a, 1) * UBound(b, 1), 1 To 2)
 
  For i = 1 To UBound(a, 1)
    For j = 1 To UBound(b, 1)
      k = k + 1
      c(k, 1) = a(i, 1)
      c(k, 2) = b(j, 1)
    Next
  Next
 
  Range("D2").Resize(k, 2).Value = c
End Sub

With excel 365 it is possible with functions, in a moment you will have help to achieve it.