small tricks of Excel

I will summarize some useful excel tricks here:

a) testing if a cell (assume A1) is an Integer

= if (int(A1)=A1,”yes”, “no”)

b) for statistics random sampling

assume you have 100,000 records, and you want to randomly select 100 records as sample

input row 1 to 100 to get 100 different random number
= rand()

use the random number  multiply by 100,000 and you get 100 randomly selected records

c) multi critiria vlookup

= vlookup($A1&$C1, “lookup range”, return result column, false)

d) If function + contains partial text “OBU” condition in cell A2

=IF(ISNUMBER(SEARCH(“OBU”,A2)),”OK”, “Not OK”)

e) nested if function

=IF(A2=”one”,1,IF(A2=”two”,2,IF(A2=”three”,3,IF(A2=”four”,4,IF(A2=”five”,5,IF(A2=”six”,6,IF(A2=”seven”,7,0)))))))+IF(A2=”eight”,… etc

f) adding 4 months to date

assume the date cell is A1

=DATE(YEAR(A1),MONTH(A1)+4,DAY(A1))

g) converting large volume of text “0812” into number “812”

A1 = 0812
=Int(A1)

result = 812
this is particularly useful when you need to convert large volume of text info into numeric info. Beware this solution doesn’t take care of decimal.

h) filling the empty rows of pivot table

datapig from Baconbits has shared a very good way to solve this. below is the link:
http://datapigtechnologies.com/blog/index.php/fill-the-empty-cells-left-by-pivot-table-row-fields/

For those without Excele 2010, you may follow the steps as below:
1) copy and paste the pivot table result as value
2) use go to special, select blank
3) type =  “desired cell”
4) ctrl + enter

i) dealing with different format of date
e.g.  excel will recognize 1/7/2013 as 7 Jan 2013, but this actual value may be 1 July 2013

In such scenario where you got thousands of records you need to adjust the date to the proper format to be recognized by excel, you may do the following:

1) use text to column with “/” as separator to split the date into 3 columns
2) use concatenate formula to join 3 different columns into the format you want

= concatenate(month column, “/”, day column, “/”, year column)
so now you will have the format as 7/1/2013

however, this is not done yet as this will be pre-defined as text, what you need to do is copy and paste in value, and use formulate = date(year(xx), month(xx), day(xx)) to convert the text format into date format.

j) dealing with complicated cell value seperation
                    Col 1                             Col 2                   Col 3
row 1 => 01xxxxxxxxxx Name
row 2 => Description with space
repeated pattern as above
.
.
.
.

you need to separate the 12 digit id and name while not affecting the row 2,

to get the ID in Col 2 => =IF(LEFT(B6,2) = “01”,LEFT(B6,12),””)
to get the Name in Col 3 => =IF(LEFT(B6,2) = “01”,RIGHT(B6,LEN(B6)-13),””)

k) create shortcut key to fill a certain color

Record a macro to fill color, set the shortcut key as Ctrl+r (or any other key you want)
Edit macro as follow:

Sub Macro2()
‘
‘ Macro2 Macro
‘
‘ Keyboard Shortcut: Ctrl+r

    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274 
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

l) small macro to do count and summation by filled color, combine to use with above macro will be handy.

Alt – F11, insert module, paste the following codes:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

Dim rCell As Range
Dim lCol As Long
Dim vResult

lCol = rColor.Interior.ColorIndex
    If SUM = True Then
        For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
        vResult = WorksheetFunction.SUM(rCell, vResult)
        End If
    Next rCell
    Else
    For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
            vResult = 1 + vResult
        End If
        Next rCell
    End If

ColorFunction = vResult

End Function

To use the functiton, fill a cell (e.g. A2) with the conditional background color, type =colorfunction(A2,”range which to be counted or sum”,True/False)

true = to count
false = to sum

m) check if partial value of the cell match with another cell value

e.g. want to check if the 13 char onward (start from “CG…..”) is equal to the other cell CG-xxxxx-E-Yxxx
use the formula as below:

=IF(RIGHT(the cell with concatenate value,LEN(the cell with concatenate value)-12)=the to be checked value,”yes”,”no”)

n) to check for 2nd or more occurrence of the lookup value

=INDEX(offset_list,SMALL(IF(offset_list=lookup value,ROW(offset_list)-ROW(Starting of offset_list)+1,ROW(Ending of offset_list)+1),no of occurence),no of column for the value to be returned)

m) text manipulating

A1 is email to: [email protected]
A2 is email to: [email protected]
A3 is email to: [email protected]

getting email address -> ==TRIM(MID(A1,FIND(“:”,A1,1)+1,100))

n) splitting 1 worksheet into multiple worksheets base on column critiria

(1) Press Alt + F11 to open up Visual Basis window
(2) insert  Module and paste the following vba code:

Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 3
Set ws = Sheets(“MasterList“)
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = “A1:W1“
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = “Unique”
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) “” And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & “”
If Not Evaluate(“=ISREF(‘” & myarr(i) & “‘!A1)”) Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).name = myarr(i) & “”
Else
Sheets(myarr(i) & “”).Move after:=Worksheets(Worksheets.Count)
End If
ws.Range(“A” & titlerow & “:A” & lr).EntireRow.Copy Sheets(myarr(i) & “”).Range(“A1”)
Sheets(myarr(i) & “”).Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub

vcol = 3   (The column of critiria which the splitting base on)
Set ws = Sheets(“MasterList“)   (the worksheet of the data)

title = “A1:W1”   (Data range of the column title)

(4) Press F5 to run the code and the worksheet will be split according to the column categories.

o) to count the number of occurrences of a text/symbol string in a cell

=LEN(A1)-LEN(SUBSTITUTE(A1,”?“,””))

replace A1 with the cell with the content and replace ? with the character/symbol that you want to count

p)  to Count the number of words separated by a space in a cell

A1: how are you?

=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)

more to come……