Category Archives: Code

Multiple into one columns and vice versa in Excel using macro

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

Sub Multiple_Single()
Dim k As Integer
Dim R As Integer

    k = 2
    R = 0

    Columns("A:A").Insert Shift:=xlToRight

    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
    Loop
End Sub

One into multiple columns:

Sub Single_Multiple()
    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", _
      Type:=8)
    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
        Loop
        x = 1
    Next
End Sub

References

  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].

Change the summary calculation of multiple pivot table fields at once using Macro (Excel)

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
With Selection.PivotTable
.ManualUpdate = True
For Each pf In .DataFields
With pf
.Function = xlSum
.NumberFormat = "#,##0"
End With
Next pf
.ManualUpdate = False
End With
End Sub

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)
Member name Description
xlAverage Average.
xlCount Count.
xlCountNums Count numerical values only.
xlMax Maximum.
xlMin Minimum.
xlProduct Multiply.
xlStDev Standard deviation, based on a sample.
xlStDevP Standard deviation, based on the whole population.
xlSum Sum.
xlVar Variation, based on a sample.
xlVarP Variation, based on the whole population.
xlUnknown No subtotal function specified.
xlDistinctCount Count using Distinct Count analysis.

References:

  1. 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].
  2. 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].

Read/edit the built-in Matlab functions

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. -

which fft

% then point Matlab to that folder on your computer so that

% the fft.m file is in your "current folder" window. Then use

% the [edit] command to edit the function, i.e. -

edit fft

% 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.