BreadCrumbs: Excel
Excel
From Luke Jackson
(Difference between revisions)
| Revision as of 01:17, 5 October 2008 (edit) Ljackson (Talk | contribs) ← Previous diff |
Revision as of 01:31, 5 October 2008 (edit) Ljackson (Talk | contribs) Next diff → |
||
| Line 1: | Line 1: | ||
| = Windows XP = | = Windows XP = | ||
| + | |||
| + | === Excel 2007 === | ||
| + | |||
| + | Data -> From Other Sources -> | ||
| == Count Unique Names / Numbers == | == Count Unique Names / Numbers == | ||
| Line 218: | Line 222: | ||
| End Function | End Function | ||
| </pre> | </pre> | ||
| + | |||
| + | == See Also == | ||
| + | |||
| + | * slala | ||
| == Souces == | == Souces == | ||
Revision as of 01:31, 5 October 2008
Contents |
Windows XP
Excel 2007
Data -> From Other Sources ->
Count Unique Names / Numbers
The entire formula is as follows (replace C3:C25 with the range of data you want the formula to check)
=SUM(IF(FREQUENCY(IF(LEN(C3:C25)>0,MATCH(C3:C25,C3:C25,0),""), IF(LEN(C3:C25)>0,MATCH(C3:C25,C3:C25,0),""))>0,1))
If you just want to count unique numbers, and not text, use the following formula instead:
=SUM(IF(FREQUENCY(C3:C25, C3:C25)>0,1))
Conditional Formating "At Change In Value"
Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Variant
Dim Clast As Variant
Dim CI As Integer
Dim ColS As Variant
Dim ColE As Variant
Dim FirstRow As Long
Dim LastRow As Long
Dim Rng As Range
Dim Wks As Worksheet
Dim Color As Boolean
Dim Total As Long
If Target.Row > 0 Then
ColS = "A"
ColE = "L"
FirstRow = 2 'Assumes header row is row 1
Set Wks = Worksheets("Scoreboard")
'LastRow = Wks.Cells(Rows.Count, ColS).End(xlUp).Row
LastRow = Target.Row
LastRow = IIf(LastRow < FirstRow, FirstRow, LastRow)
Set Rng = Wks.Range(Cells(FirstRow, ColS), Cells(LastRow, ColS))
For Each C In Rng
If IsDate(C) Then
If Clast = "" Then
CI = 50
Clast = C
'MsgBox "Start " & C & " - " & Clast
ElseIf C <> Clast And Color = False Then
CI = xlColorIndexNone
Color = True
'MsgBox "CF CNE " & C & "<>" & Clast
Clast = C
ElseIf C <> Clast And Color = True Then
CI = 50
Color = False
'MsgBox "CT CNE " & C & "<>" & Clast
Clast = C
ElseIf C = Clast And Color = False Then
CI = 50
'MsgBox "CF CE " & C & "=" & Clast
Clast = C
ElseIf C = Clast And Color = True Then
CI = xlColorIndexNone
'MsgBox "CT CE " & C & "=" & Clast
Clast = C
Else
CI = xlColorIndexNone
MsgBox "Else " & C
End If
If CI > 0 Then
With Range(Cells(C.Row, ColS), Cells(C.Row, ColE)).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Else
With Range(Cells(C.Row, ColS), Cells(C.Row, ColE)).Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
End If
Total = Total + 1
Next C
'MsgBox Total
End If
End Sub
Function
=IF(INDIRECT(ADDRESS(ROW(),1,3))<>INDIRECT(ADDRESS((ROW()-1),1,3)),1,0)
Date Filters Not Available for Pivot Table / Chart
Date Filters are not supported in Excel 2003 compatibility mode.
Re-save document in Excel 2007 format and the options will no longer be greyed out.
Mac OS X
File Not Found
When starting Microsoft Excel 2004 for Mac OS X you may get the error "File Not Found".
First place to check is the Excel Startup folder:
ljackson 14:46:50 ~/Library/Preferences> cd /Applications/Microsoft\ Office\ 2004/Office/Startup/ ljackson 14:47:33 /Applications/Microsoft Office 2004/Office/Startup> ll drwxrwxr-x 4 ljackson admin 136B May 3 14:39 Excel drwxrwxr-x 2 ljackson admin 68B Apr 8 2004 PowerPoint drwxrwxr-x 2 ljackson admin 68B Apr 8 2004 Word ljackson 14:47:33 /Applications/Microsoft Office 2004/Office/Startup> cd Excel/ ljackson 14:47:37 /Applications/Microsoft Office 2004/Office/Startup/Excel> ll total 96 -rw-r--r-- 1 ljackson admin 25K May 3 14:15 8DAE9700 -rw-r--r-- 1 ljackson admin 16K May 3 14:39 Personal Macro Workbook
In my case the alphanumerical file was generated when Excel crashed. So I will delete it:
ljackson 14:47:38 /Applications/Microsoft Office 2004/Office/Startup/Excel> rm -f 8DAE9700 ljackson 14:47:53 /Applications/Microsoft Office 2004/Office/Startup/Excel> ll total 40 -rw-r--r-- 1 ljackson admin 16K May 3 14:39 Personal Macro Workbook
Now I don't get the anoying error "File Not Found" every time I launch Excel.
VBA Macros
Sub RangeDateFix()
Dim ndate As String
'ActiveCell.CurrentRegion.Cells.Select
For Each c In ActiveWindow.RangeSelection.Cells
ndate = USdateEU(c.Value)
c.Value = ndate
Next
End Sub
Public Function USdateEU(ByVal tdate As String)
Dim txt As String, ftxt As String, x As Variant, i As Long
txt = tdate
x = Split(txt, "/")
'For i = 0 To UBound(x)
'MsgBox x(i)
'Next i
ftxt = x(1) & "/" & x(0) & "/" & x(2)
USdateEU = ftxt
'MsgBox ftxt
End Function
Public Function Split(ByVal sInput As String, _
Optional ByVal sDelimiter As String, _
Optional ByVal nLimit As Long = -1, _
Optional ByVal bCompare As Integer = vbBinaryCompare _
) As Variant
Dim nCount As Long
Dim nPos As Long
Dim nDelimiterLength As Long
Dim nStart As Long
Dim sOutput() As String
If nLimit = 0 Then
Split = Array()
Else
nDelimiterLength = Len(sDelimiter)
If nDelimiterLength = 0 Then
Split = Array(sInput)
Else
nStart = 1
nPos = InStr(nStart, sInput, sDelimiter, bCompare)
Do While nPos
ReDim Preserve sOutput(0 To nCount) As String
If nCount + 1 = nLimit Then
sOutput(nCount) = Mid(sInput, nStart)
Exit Do
Else
sOutput(nCount) = Mid(sInput, nStart, nPos - nStart)
nStart = nPos + nDelimiterLength
End If
nCount = nCount + 1
nPos = InStr(nStart, sInput, sDelimiter, bCompare)
Loop
ReDim Preserve sOutput(0 To nCount) As String
sOutput(nCount) = Mid(sInput, nStart)
Split = sOutput
End If
End If
End Function
See Also
* slala