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
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
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 : )