Highlight the row containing the names and select PivotTable and PivotChart Report from the Data menu. Click on Next and then click on Finish. This will create an empty PivotTable in a new worksheet. Drag the Name field into the Drop Row Fields Here area, then drag it into the Drop Data Items Here area. Now you have a list of all the unique names, along with the number of times each name occurs.
To get a count of unique names, enter this formula in a cell that's not in column A: =COUNTA(A:A)-3.
COUNTA returns the number of nonblank cells in the column. The two PivotTable headers and the grand total line are not counted, so we subtract 3.
One warning: PivotTables are not recalculated automatically. If the list of names changes, click on the Refresh Data button (the red exclamation point) in the PivotTable toolbar.
Assume col label (eg: Name) is in A1, data in A2 downwards
Select col A
Click Data > Filter > Advanced Filter
In the dialog box:
Check "Copy to another location"
Put for "Copy to:" : B1
Check "Unique records only"
Click OK
To get a count of unique names, enter this formula in a cell that's not in column A: =COUNTA(A:A)-3.
You can change the number of undo levels in Excel to any value from 0 to 100. The higher the number, the more resources the software will use to remember the previous states. To configure the number of undo levels, perform the following steps:
1. Start a registry editor (e.g., regedit.exe).
2. 2. Navigate to the HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options registry subkey for Excel 2002, or navigate to the HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Options registry subkey for Excel 2000.
3. From the Edit menu, select New, DWORD Value.
4. Enter the name UndoHistory, then press Enter.
5. Double-click the new value, set it to a value from 0 to 100, then click OK.
6. Close the registry editor.
Save the Excel file as tab-delimited text, and load the resulting file into Word. Search for tab (^t) and replace each occurrence with </TD><TD>. Search for end-of-line (^p) and replace each instance with </TD></TR>^p<TR><TD>. You now have an extra <TR><TD> on a blank line at the bottom, and the same text missing at the start of the very first line. Move the extra text to the beginning of the first line. Add the <TABLE> tag at the top and </TABLE> at the end and you're ready to copy the document into your HTML page
=CELL("FILENAME",F10)
eg. H:\MSOffice\Macros\[Accessing document properties.xls]Sheet1
=MID(CELL("FILENAME",F8), 1,FIND("[",CELL("FILENAME",F8))-1)
eg. H:\MSOffice\Macros\
strFileName = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt,All Files (*.*),*.*")
ActiveWorkbook.SendMail Recipients:="ab@ab.com", Subject:=strSubject
MsgBox "Mail sent - please check to verify that it was actually delivered.", vbInformation
To base of row of attributes on a cell's value, use the conditional formatting feature.
Suppose you have a range of cells starting at A1, and you want all cells in a row to be red if the value in column B is over 90.
Select cell A1, and choose Conditional Formatting from the Format menu. Set the condition to Formula Is, and enter =$B1>90.
Click the Format... button, set the font color to red, and click OK, OK.
With A1 still selected, click the Format Painter button on the toolbar, then select the entire range.
The $ character preceding the column address in the formula marks it as absolute, so each cell's formatting will be based on the cell in column B in the same row.
(Note: although you can specify up to three conditions for conditional formatting, the first condition that evaluates to TRUE will control the formatting.)
Back to Top
A Few more Tips
Sometimes when trying to enter something into an Excel cell and before you've committed to it, you realize that you've done something really dumb. Technically, you can't undo it because you haven't pressed the Enter key yet. But to restore the entry that was in the cell press the Esc key. The previous cell contents are restored.
Normally, when you type an equal sign (=) in Excel, it assumes you are going to follow that with a formula. Most of the time, that's a good thing. But here's an interesting problem: what if you really want to type an equal sign? Uh oh. The answer is that before you type your equal sign, you need to type an apostrophe, so that you enter '=. When you do this, you are telling Excel that you really are entering an equal sign and not a formula.
After you press Enter, the apostrophe disappears, so it doesn't display.
To have Excel show you a drop-down list of the content that has previously been typed in a column.
Click the cell and then press Alt+Down Arrow. You see a list of all the entries that have been added in that column. Let go of the Alt key and use the up and down arrow keys to select the entry you want. Now press Enter.
How to organize a lottery pool and track it with a spreadsheet. Have one column listing the names of the players, each of whom picks six numbers from 1 to 49. After the drawing, enter the six numbers in a separate row at the top of the page. To make the spreadsheet highlight any matches in each player's row, follow steps below?
The trick is to use just one condition in your conditional formatting. Suppose the names are in column A starting at A2, with each player's picks in columns B through H. Then suppose that the current set of correct numbers is in B1 through G1. Select cell B2 and choose Conditional Formatting from the Format menu. Select Formula Is in the drop-down list, and enter the formula =COUNTIF($B$1:$G$1,B2)>0. This formula will return TRUE when the value of the cells appears in the list of winning numbers.
Click on the Format button and define the special formatting you want to use for matches, then click on OK and OK again. Click on the Format Painter button on the toolbar and apply this formatting to the rest of the lotto numbers. Now all matches will use the special formatting you defined.
As for counting the number of hits, this is a great opportunity to use a little-known Excel feature called an array formula. In cell H2, type =SUM(COUNTIF(B2:G2,$B$1:$G$1)), but instead of pressing Enter, press Ctrl-Shift-Enter. Excel will put curly brackets around the formula to indicate that it's an array formula. (And no, you can't get the same effect by entering the curly brackets yourself.) The COUNTIF() function is calculated using each element of the $B$1:$G$1 array (the actual winning numbers), and the SUM() function totals the counts. Now copy this formula down column H into the rest of the player rows.
You can save selected charts as GIF files using the following macro:
Back to TopSub savechart() If TypeName(Selection) = "ChartArea" Then userFname = InputBox("Filename of chart" _ & " file?", "Save chart", "excelchart") If userFname = "" Then Exit Sub userNameAndPath = ThisWorkbook.Path & "\" & userFname & ".gif" ActiveChart.Export Filename:=userNameAndPath, FilterName:="GIF" MsgBox "Chart is saved as" & Chr(13) & userNameAndPath Else userReply = MsgBox("Please select a Chart Area, " _ & "then run macro again", vbOKOnly, "Error in selection") End If End Sub
|
Application.ScreenUpdating = False
Set wksDataSheet = ActiveSheet
Set rngRange = wksDataSheet.UsedRange intNumRows = wksDataSheet.UsedRange.Rows.Count intColRows = wksDataSheet.UsedRange.Columns.Count
Range("A1").Select ActiveRow = 2 ActiveRow2 = 3
ActiveCol = Chr(65) 'returns "A"
Do While Asc(ActiveCol) < 68
Do While ActiveRow2 <= intNumRows ActiveRowS = LTrim(Str(ActiveRow)) Range(ActiveCol + ActiveRowS).Activate
str1 = Range(ActiveCol + CStr(ActiveRow)).Value
str2 = Range(ActiveCol + CStr(ActiveRow2)).Value
If str1 = str2 Then str2 = "" Range(ActiveCol + CStr(ActiveRow2)).Value = str2 ActiveRow2 = ActiveRow2 + 1 Else ActiveRow = ActiveRow2 ActiveRow2 = ActiveRow2 + 1 End If Loop
ActiveRow = 2 ActiveRow2 = 3 ActiveCol = Chr(Asc(ActiveCol) + 1)
Loop
'''''''End LOOP'''''''''''''
|
Back to Top
WORD TIPS
Recover a
Document if Word Crashes
Go to the Tools menu, then select Options and click the File Locations tab. Empty space next to AutoRecover files means your data is lost. If there is a location, go there and open the document.
You can create a line across the page of your Word or Outlook document with just a few keystrokes.
Type three consecutive hyphens and press Enter to get a normal line.
Type three underscores and Enter, and you'll get a bold line.
And if you type three equal signs and press Enter, you'll get a double line.
The feature you're looking for generates as much text as you ask for, printing the sentence The quick brown fox jumps over the lazy dog as many times as you need. To generate the text, simply open a new blank document and type the command =rand(p,s), where p is the number of paragraphs you want and s is the number of sentences per paragraph. Then press Enter, and Word will create the text.
This technique will work anywhere in a document as long as you enter the string so that the equals sign is the first text character in a paragraph. For example, suppose you want to create a quick and dirty table to experiment with anywhere in your document. For a three-column table, first enter the command =rand(3,1), which creates three paragraphs containing one sentence each.
Next, select the three paragraphs and choose Table | Convert | Text to Table. Set the number of columns to 3, make sure the conversion is set to separate text at paragraphs, and choose OK. This will produce a one-row, three-cell table with text in each cell and the row selected. Copy the selected text and create as many rows as you need by pasting them in.
The default font for the Normal.dot template is Times New Roman. To have all your New documents use a different font automatically (for example, Arial), click Font from the Format menu. The Font dialog displays. In the Font field, scroll the list of typefaces until the font you want displays in the Font window. Click this font. Select the appropriate Font style, size settings, and font color, and then click the Default button at the bottom of the Font dialog box. Word pops up an alert box asking if you want to change the default font to the selection you've made and advising you that the change you are about to make will affect all new documents based on the Normal.dot template. Click Yes.
Vertical Selecting Have you ever wished you could select a tabbed column of numbers or a vertical chunk of text in Word? Maybe you thought you had to select each item separately because you couldn't highlight just one column without highighting other areas. What a pain! Guess what? You can select text vertically in Word! Just click at the beginning of your selection and hold down the Alt key while you drag your mouse across and down to select a vertical chunk of text.
To add an item to the My Places bar, follow these steps:
On the File menu in any Office XP program, click Open.
In the Open dialog box, select the item that you want to add in the contents
pane.
Example: In the Look in list, click My Documents. In the contents pane,
click My Pictures. Click Tools, and then click Add to "My Places".
NOTE: You can add up to 256 items to the My Places bar. You can also remove and
rename items, although default items cannot be removed or renamed.
For more info: check 282087
To print a C and an equal sign in the same spot, similar to the Euro symbol, which combines a C and an equal sign, follow these steps:
First, press Ctrl-F9 to insert the curly brackets that surround a field code. Between the brackets, type eq \o(N,=). Press Alt-F9 to toggle from displaying field codes to displaying field results.
The eq field is used to create equations, and the \o switch tells Word to overstrike the next characters within the parentheses. You can put as many characters in the parentheses as you want. There are other switches for displacement, as well as for creating fractions, integrals, alignment, and so on. Search for the eq field in Help for more information.
For those of you who paste a lot of graphics files into your Word XP (Word 2002) documents, have you ever noticed how big those Word document files get? We work with a lot of Word docs containing tons of graphics and they get really big. Here's a tip that will shrink those Word docs down ten-fold!(from winXP)
Go Back or Return to home page.