Imagine that we have the following 2 lists each consisting of 3 columns of data: And we need to find those items that are in List 1 that are in List 2. However ALL 3 columns of data must match for items to be in both lists. One way would be to construct a comprehensive [...]

This is the opposite tutorial of the Converting a Single Row Vector into a Matrix tutorial. The following function will take a range as a matrix and convert it to a single column vector: Option Explicit Function Create_Vector(Matrix_Range As Range) As Variant Dim No_of_Cols As Integer, No_Of_Rows As Integer Dim i As Integer Dim j [...]

Imagine that we have a list of names such as: And we want to give each one a unique identifier: So that the first name Bob has the identifier 1, and the next Mark has the identifier 2. This can be achieved by the following process. We assign Bob (i.e the first person in the [...]

Excel’s SUBSTITUTE function allows us to substitute part of a string with another part: SUBSTITUTE(String,”Old Text”,”New Text”,occurrence”) Where: • String is the string that we are working with • “Old Text” is the text that we want to eliminate • “New Text” is the text that we want to incorporate • Occurrence is which instance [...]

This is the opposite tutorial of the Convert Matrix to Vector tutorial. The following function takes a single row vector and converts into a matrix: Function Create_Matrix(Vector_Range As Range, No_Of_Cols_in_output As Integer, No_of_Rows_in_output As Integer) As Variant ReDim Temp_Array(No_Of_Cols_in_output, No_of_Rows_in_output) Dim No_Of_Elements_In_Vector As Integer Dim Col_Count As Integer, Row_Count As Integer Dim Cell No_Of_Elements_In_Vector = [...]

The following function will return the Maximum Value in each Column in a Range: Function Max_Each_Column(Data_Range As Range) As Variant Dim TempArray() As Double, i As Long If Data_Range Is Nothing Then Exit Function With Data_Range ReDim TempArray(1 To .Columns.Count) For i = 1 To .Columns.Count TempArray(i) = Application.Max(.Columns(i)) Next End With Max_Each_Column = TempArray [...]

The following tutorial will describe how to create a bar chart using VBA. Steps: 1. Enter the VBA project window by right clicking on a sheet name and selecting “View Code” or by selecting “ALT, F11”. 2. On the right hand side, right click on your project name and select inset “module”. 3. Copy and [...]

A simple way to add a comment to a formula for later reference is to add the following to the formula N+(“YOUR COMMENT”). By way of an example, =SUM(A2:A4)+N(“This is”).

The following Subroutine will delete each row in a range where the value in Column A begins with a prescribed piece of text: Sub Delete_Rows(Data_range As Range, Text As String) Dim Row_Counter As Integer For Row_Counter = Data_range.Rows.Count To 1 Step -1 If Data_range Is Nothing Then Exit Sub End If If UCase(Left(Data_range.Cells(Row_Counter, 1).Value, Len(Text))) [...]

Consider the following two lists: And that we want to see which items from column A are in Column B. This can be achieved by the MATCH function in conjunction with ISNUMBER . The MATCH function has the following syntax: MATCH(A6,C4:C13,0) And this will return the position of the value cell A6 in the range [...]