Is there a function where I can merge all columns

Question

I have a situation in Excel where I want to combine all values of column A to to all values of column B (where I want to keep all values of B Absolute to All values of column A)

Example: Column A - 1 2 3 4 5 6 7 8 Column B - A B C D E F G H

The result I want is 1A 2A 3A 4A 5A 6A 7A 8A 1B 2B 3B 4B 5B so on till I reach 8H

I have tried simple concatenating but obviously it is taking too long

It would be appreciated if someone can look into this and provide me with a formula to help me fix this.

Regards

Answer 1

After your post, I realized what you are really after.

In cell C1 put:

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

And drag down

Answer 2

I've found this sollution, but in VBA. I hope it's okay for you. That's how it look on my excel

Sub Merge()

Dim NumberLeng As Range
Dim LetterLeng As Range
Dim LastLetter As Long
Dim LastNumber As Long
Dim sht As Worksheet
Dim LastRow As Long
Dim LastMix As Long
Set sht = ActiveSheet
LastLetter = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row 'last letter
LastNumber = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row 'last number

Set NumberRange = sht.Range(Cells(1, 1), Cells(LastNumber, 1)) ' range of numbers
Set LetterRange = sht.Range(Cells(1, 2), Cells(LastLetter, 2)) ' range of letters
X = 0
    For Each Letter In LetterRange

        For Each Number In NumberRange

        Number.Offset(X, 2) = Number & Letter 'Number+Letter

        Next Number

    LastMix = sht.Cells(sht.Rows.Count, "C").End(xlUp).Row 'set new last
    X = LastMix

    Next Letter

End Sub

If there is any help needed go ahead and ask : )