CODEWITHSUNDEEP
excelvba
Ela
Ela

Reputation: 11

Excel vba multiple columns into one cell

ROW1| 1 2 3
ROW2| A D G
ROW3| B E H
ROW4| C F I

I want output like this:

ROW1| 1
ROW2| A
ROW3| B
ROW4| C
ROW1| 2
ROW2| D
ROW3| E
ROW4| F
ROW1| 3
ROW2| G
ROW3| H
ROW4| I

Please help me

Upvotes: 0

Views: 387

Answers (1)

Mrig
Mrig

Reputation: 11727

Try this formula:

=IFERROR(INDEX($A$1:$C$4,MOD(ROW()-1,4)+1,INT((ROW()-1)/4)+1),"")

and drag/copy down as required.

enter image description here

VBA Solution:

Sub RowsToColumn()
    Dim dict As Object
    Dim lastRow As Long, lastColumn As Long, cnt As Long
    Dim myRng As Range

    Set dict = CreateObject("Scripting.Dictionary")

    'get last row and column with data
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    'set your range here
    'Set myRng = Range("A1:C4")
    Set myRng = Range(Cells(1, 1), Cells(lastRow, lastColumn))

    cnt = 1
    'put cell value in dictionary
    For Each col In myRng.Columns
        For Each cel In col.Cells
            dict.Add cel.Value, cnt
            cnt = cnt + 1
        Next cel
    Next col
    'display values in dictionary
    Range("E1").Resize(dict.Count) = Application.Transpose(dict.keys)
End Sub

Upvotes: 1

Related Questions