How To Put Data From Multiple Columns Into One Column In Excel For Mac

Posted on  by

Stack multiple columns into one with Transform Range Stack multiple columns into one with formula 1. Select the range you want to stack, and go to the Name Box to give this range a name, and press Enter key. Select the range of the columns you want to be copied to single column Copy the range of cells (multiple columns). Method 1: Stack Data in Multiple Columns into One Column by Formula Step 1: Select range A1 to F2 (you want to do stack), in Name Box, enter a valid name like Range, then click Enter. Step 2: In any cell you want to locate the first cell of destination column, enter the formula = INDEX (Range,1+ INT (( ROW (A1)-1)/ COLUMNS (Range)), MOD (ROW.

In case you have a requirement on combining multiple columns to on column & you did not have a clue then this whole article is for you. In this article we are going to learn how to combine multiple columns to one column using vba code.

From below snapshot:-

Following is the snapshot of require output:-

We need to follow the below steps:

  • Click on Developer tab
  • From Code group select Visual Basic

Enter the following code in the standard module

Sub MultipleColumns2SingleColumn()

Const shName1 As String = 'Sheet1' 'Change sheet name here

How to put data from multiple columns into one column in excel for mac osx

Const shName2 As String = 'Sheet2'

Data

Dim arr, arrNames

How To Put Data From Multiple Columns Into One Column In Excel For MacExcel

With Worksheets(shName1)

arrNames = .Range('F1', .Cells(1, Columns.Count).End(xlToLeft))

For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row

arr = .Cells(i, 1).Resize(, 4)

With Worksheets(shName2)

With .Cells(Rows.Count, 1).End(xlUp)

.Offset(1).Resize(UBound(arrNames, 2), 4) = arr

.Offset(1, 5).Resize(UBound(arrNames, 2)) = Application.Transpose(arrNames)

End With

End With

Next

End With

How To Put Data From Multiple Columns Into One Column In Excel For Mac Shortcut

End Sub

As you execute the macro; the macro will transfer the data from multiple columns to a single column.

How To Put Data From Multiple Columns Into One Column In Excel For Mac Shortcut

In this way we can combine multiple columns data in a single column.