If you work with a lot of data in Excel, it is difficult to rearrange multiple column of data into a single column. The traditional way, you cut/copy and paste, and it will take forever (boring and repetitive works). The simple way, you able to used “macro”. Below are codes for macro in Excel for rearrange multiple columns into one and vice versa, respectively.
Multiple into one columns (Ozgrid.com, 2007):
Dim k As Integer
Dim R As Integer
k = 2
R = 0
Do Until R > 65536 Or Cells(65536, k).End(xlUp).Value = ""
R = R + Range(Cells(1, k), Cells(65536, k).End(xlUp)).Rows.Count
Range(Cells(1, k), Cells(65536, k).End(xlUp)).Copy Range("A65536").End(xlUp).Offset(1, 0)
k = k + 1
One into multiple columns:
Dim rng As Range
Dim iCols As Integer
Dim lRows As Long
Dim iCol As Integer
Dim lRow As Long
Dim lRowSource As Long
Dim x As Long
Dim wks As Worksheet
Set rng = Application.InputBox _
(prompt:="Select the range to convert", _
iCols = InputBox("How many columns do you want?")
lRowSource = rng.Rows.Count
lRows = lRowSource / iCols
If lRows * iCols <> lRowSource Then lRows = lRows + 1
Set wks = Worksheets.Add
lRow = 1
x = 1
For iCol = 1 To iCols
Do While x <= lRows And lRow <= lRowSource
Cells(x, iCol) = rng.Cells(lRow, 1)
x = x + 1
lRow = lRow + 1
x = 1
- Ozgrid.com, (2007). Move Multiple Columns From Multiple Worksheets Into 1 Column. [online] Available at: http://www.ozgrid.com/forum/showthread.php?t=75449 [Accessed 17 Dec. 2014].
Pivot table is useful to summary calculation for table. However, there isn’t a simple way to adjust multiple fields to the same function. The traditional way, you can right-click on the pivot table and adjust one-by-one.
Fortunately, there is the easy way to change multiple pivot-fields to the same function, by using the macro-excel. The full reference can be obtained from Moxie (2013). Here is a short macro that converts all the fields in a selected pivot table to the Sum function (code as xlSum) as follow:
Figure 1: Macro code for adjust multiple fields to the same function (reproduced from Moxie(2013)) (Note: Make sure you have selected the pivot table to change before playing the macro)
Public Sub PivotFieldsToSum()
' Cycles through all pivot data fields and sets to sum
' Created by Dr Moxie
Dim pf As PivotField
.ManualUpdate = True
For Each pf In .DataFields
.Function = xlSum
.NumberFormat = "#,##0"
.ManualUpdate = False
You can change xlSum with other function such as: xlAverage, xlCount, xlCountNums, xlMax, xlMin, xlProduct, xlStDev, xlStDevP, xlSum, xlVar and xlVarP. The detail of each function is illustrated in Table 1.
Table 1: Detail of XlConsolidationFunction enumeration (MSDM Microsoft, 2014)
||Count numerical values only.
||Standard deviation, based on a sample.
||Standard deviation, based on the whole population.
||Variation, based on a sample.
||Variation, based on the whole population.
||No subtotal function specified.
||Count using Distinct Count analysis.
- Moxie, D. (2013). How to change multiple pivot table fields to Sum Function – Excel Pivots. [online] Excel Pivots. Available at: http://excelpivots.com/excel/change-multiple-pivot-table-fields-sum-function/ [Accessed 30 Nov. 2014].
- MSDM Microsoft (2014). XlConsolidationFunction enumeration (Microsoft.Office.Interop.Excel). [online] Available at: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlconsolidationfunction.aspx [Accessed 30 Nov. 2014].
I found a good tip from the matlab community to find out the built-in function in Matlab. You can edit and change it. The tip as follows:
% You can use the [which] command to find the path name to the
% built-in function in question, i.e. -
% then point Matlab to that folder on your computer so that
% the fft.m file is in your "current folder" window. Then use
% the  command to edit the function, i.e. -
% The code for the fft function will appear in the editor window.
% Often it is useful to make small changes to the built-in code
% then comment out the changes as soon as you're done so that
% Matlab will continue to function as designed.