need solution for the following , in excel 2019

[raghunathreddy]

Column1	Column2		Output
1	a		1a
2	b		1b
3	c		1c
4	d		1d
5	e		2a
			2b
			2c
			2d
			3a
			3b
			3c
			3d
			4a
			4b
			4c
			4d

 

[Legacy 143009]

Assuming result column is F, F1 formula will be:

=IF(ROW()-ROW($F$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($F$1))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1))

 

[raghunathreddy]

Thanks Its Working

 

[Legacy 143009]

You are welcome

 

[mumps]

VBA approach:

Sub test()
    Application.ScreenUpdating = False
    Dim rng As Range, srcRng As Range, lRow As Long, v As Variant, arr() As Variant, i As Long, ii As Long, x As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("A1:A" & lRow).Resize(, 2).Value
    For i = LBound(v) To UBound(v)
        For ii = 1 To lRow
            x = x + 1
            ReDim Preserve arr(1 To x)
            arr(x) = v(i, 1) & v(ii, 2)
        Next ii
    Next i
    Range("C1").Resize(x - 1) = Application.Transpose(arr)
    Application.ScreenUpdating = True
End Sub