Maxima Report Analyze

Download

Number to Text Convert Formula for 0 to 99

For Example: 2   - Two

22 - Two Two

58 - Five Eight

79 - Seven Nine

96 - Nine Six

Formula:

=IF(LEN(A1)=1,VLOOKUP(A1,{0,"Zero";1,"One";2,"Two";3,"Three";4,"Four";5,"Five";6,"Six";7,"Seven";8,"Eight";9,"Nine"},2,FALSE),VLOOKUP(VALUE(LEFT(A1,1)),{0,"Zero";1,"One";2,"Two";3,"Three";4,"Four";5,"Five";6,"Six";7,"Seven";8,"Eight";9,"Nine"},2,FALSE)&VLOOKUP(VALUE(RIGHT(A1,1)),{0,"Zero";1,"One";2,"Two";3,"Three";4,"Four";5,"Five";6,"Six";7,"Seven";8,"Eight";9,"Nine"},2,FALSE))

Staff Training Programme

                                                               Download Link

Numbers to Text Converting Formula

=TRIM(IF(A1<1000,"",IF(MOD(A1,1000000)>=100000,INDEX({"";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine";"Ten";"Eleven";"Twelve";"Thirteen";"Fourteen";"Fifteen";"Sixteen";"Seventeen";"Eighteen";"Nineteen"},TRUNC(MOD(A1,1000000)/100000,0)+1)&" Hundred","")&IF(MOD(A1,100000)>=20000," "&INDEX({"";"";"Twenty";"Thirty";"Forty";"Fifty";"Sixty";"Seventy";"Eighty";"Ninety"},TRUNC(MOD(A1,100000)/10000,0)+1)&IF(MOD(MOD(A1,100000),10000)>=1000," "&INDEX({"";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine";"Ten";"Eleven";"Twelve";"Thirteen";"Fourteen";"Fifteen";"Sixteen";"Seventeen";"Eighteen";"Nineteen"},TRUNC(MOD(MOD(A1,100000),10000)/1000,0)+1),""),IF(MOD(A1,100000)>=1000," "&INDEX({"";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine";"Ten";"Eleven";"Twelve";"Thirteen";"Fourteen";"Fifteen";"Sixteen";"Seventeen";"Eighteen";"Nineteen"},TRUNC(MOD(A1,100000)/1000,0)+1),""))&" Thousand") & IF(A1<1,"Zero",IF(MOD(A1,1000)>=100," "&INDEX({"";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine";"Ten";"Eleven";"Twelve";"Thirteen";"Fourteen";"Fifteen";"Sixteen";"Seventeen";"Eighteen";"Nineteen"},TRUNC(MOD(A1,1000)/100,0)+1)&" Hundred","")&IF(MOD(A1,100)>=20," "&INDEX({"";"";"Twenty";"Thirty";"Forty";"Fifty";"Sixty";"Seventy";"Eighty";"Ninety"},TRUNC(MOD(A1,100)/10,0)+1)&IF(MOD(MOD(A1,100),10)>=1," "&INDEX({"";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine";"Ten";"Eleven";"Twelve";"Thirteen";"Fourteen";"Fifteen";"Sixteen";"Seventeen";"Eighteen";"Nineteen"},TRUNC(MOD(MOD(A1,100),10),0)+1),""),IF(MOD(A1,100)>=1," "&INDEX({"";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine";"Ten";"Eleven";"Twelve";"Thirteen";"Fourteen";"Fifteen";"Sixteen";"Seventeen";"Eighteen";"Nineteen"},TRUNC(MOD(A1,100),0)+1),""))&""))

Download Link

Insert the rows in between the name list:

1. Add new "helper column"
2. Type "A"&"B" in helper columns cells
3. Select both cells & Drag it down
4. Data --> Filter ---> Select "A"
5. Select helper column
6. Press "Alt ;" to select only visible cells
7. Press Ctrl+Shift+Plus and Select "Entire Row" then Press OK
8. Data --> Filter ---> Select "B"
9. Select helper column
10. Press "Alt ;" to select only visible cells
11. Press Ctrl+Shift+Plus and Select "Entire Row" then Press OK

Check this link, http://www.screenr.com/J6mH

First-In, First-Out (FIFO) Method Calculation

Download Link

Highlight Duplicates

Sub ColorDuplicates()
'Color duplicate items between Sheet1.columns A and Sheet2.Column A
    For i = 1 To Sheets("Sheet1").Range("A65536").End(xlUp).Row
         If Application.WorksheetFunction.CountIf(Sheets("Sheet2").Range("A:A"), Sheets("Sheet1").Range("A" & i)) = 1 Then
            With Sheets("Sheet1").Range("A" & i).Font
                .ColorIndex = 3
                .Bold = True
            End With
         Else
            With Sheets("Sheet1").Range("A" & i).Font
                .ColorIndex = 1
                .Bold = False
            End With
         End If
    Next i
    For i = 1 To Sheets("Sheet2").Range("A65536").End(xlUp).Row
         If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("A:A"), Sheets("Sheet2").Range("A" & i)) = 1 Then
            With Sheets("Sheet2").Range("A" & i).Font
                .ColorIndex = 3
                .Bold = True
            End With
         Else
            With Sheets("Sheet2").Range("A" & i).Font
                .ColorIndex = 1
                .Bold = False
            End With
         End If
    Next i
End Sub

Download Link

Extracting Text from Alphanumeric Strings

=SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0",""),"1",""),"2",""), "3",""),"4",""),"5",""), "6",""),"7",""),"8",""),"9","")

Download Link

Extracting Numbers from Alphanumeric Strings

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),COUNT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))+SUM(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=".")))

Download Link

VBA Code for Insert Row

Sub InsRow()

Dim lastrow As Long, r As Long

lastrow = ActiveSheet.UsedRange.Rows.Count

For r = lastrow To 3 Step -1

If Cells(r, 1).Value <> "" Then Rows(r).Insert

Next r

End Sub

Download Link

AUTOMATIC QUOTATION FORMAT

Download Link

CCTV & SACS SYSTEM

Download Link

PMS CHECK LIST

Download Link

SERVICE SYSTEM

Download Link

AMC MANAGEMENT SYSTEM

Download Link

MTA MANAGEMENT SYSTEM

Download Link

AUTOMATIC INVOICE SYSTEM

 

Download Link 

VBA Code for Image Insert in Excel

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Picture As Object
If Target.Cells.Count > 1 Then Exit Sub
With Target.Offset(0, 1)
    Set Picture = Nothing
    On Error Resume Next
    Set Picture = Sheets("SMW").Pictures.Insert(ActiveWorkbook.Path & "\" & Target.Value & ".jpg")
    Picture.Top = .Top
            Picture.Left = .Left
            Picture.ShapeRange.LockAspectRatio = msoFalse
            Picture.Placement = xlMoveAndSize
            Picture.ShapeRange.Width = 50
            Picture.ShapeRange.Height = 44
End With
End Sub

Microsoft Excel Function Reference

Database Functions

DAVERAGE(database,field,criteria)

Averages the values in a column in a list or database that match conditions you specify.

DCOUNT(database,field,criteria)

Counts the cells that contain numbers in a column in a list or database that match conditions you specify.

DCOUNTA(database,field,criteria)

Counts all of the nonblank cells in a column in a list or database that match conditions you specify.

DGET(database,field,criteria)

Extracts a single value from a column in a list or database that matches conditions you specify.

DMAX(database,field,criteria)

Returns the largest number in a column in a list or database that matches conditions you specify.

DMIN(database,field,criteria)

Returns the smallest number in a column in a list or database that matches conditions you specify.

Remove the Space

Sub TrimALL()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub