28 Jun 2014
10 Oct 2013
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))
5 Oct 2013
25 Aug 2013
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
Download Link
20 Aug 2013
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
14 Aug 2013
30 Jun 2013
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
'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
Subscribe to:
Posts (Atom)