The account number has to be to the left of your numeric data. If your range includes 12 monthly columns, the answer will have totals for each month.Ĭons: If you do another Data Consolidate on the same sheet, you need to clear the old range name out of the All References field using the Delete button. Shortcut is alt-D N (rangename) alt-T alt-L enter. In the Use Labels In section, check both Top Row and Left Column.In the reference field, type the range name (TotalMe).Put the cell pointer in a blank section of the worksheet.Assign a range name to this area by clicking in the name box (to the left of the formula bar) and typing a name such as "TotalMe".You need to assign a range name to the rectangular block of cells that include the account numbers along the left column and the headings along the top. You have to have headings above each column. This was BIG! It takes 30 seconds to set it up, but it spelled death for DSUMs and other methods.Your account number has to be to the left of the numeric fields you want to total. My quality of life improved when Excel offered Data Consolidate. No sorting required.Ĭons: The CSE formulas required after this will make your head spin. Enter any downline manipulations, array formulas, etc.The unique account numbers will appear in F1. (Note this field is greyed out until you pick "Copy to another location". Pick a blank section of the worksheet where you want the unique list to appear.Click the check box for "Unique Records Only". Click the radio button for "Copy to another location".From the Menu, pick Data, Filter, Advanced Filter.This is a method for getting a list of the unique account numbers. Data Filterīill's question was really how to get a unique list of account numbers so that he could use CSE Formulas to get the totals. Method 2 Use Data Filter - Advanced Filter to get the list of unique accounts. All you need is a keen sense of writing IF statements. For the rows which have a TRUE in column D, you have a unique list of account numbers in A, and the final running total in C.Do an Edit - PasteSpecial - Values back onto C2:D100 to change the formulae to values. Invent a formula in D which will identify the last entry for a particular account.Invent a formula in column C which will keep a running total by account.I used to use this a lot before better things came along and there are still situations where it comes in useful. Given the newer tools offered by Excel, I no longer recommend this method. Method 1 Use creative If statements in conjunction with Paste Special Values to find the answer. My simplified data set has account numbers in column A and amounts in column B. I will offer a tutorial on the five methods this week. If you are using Excel 97, there are at least five methods to do this task, all of which are far easier than the classic method described by Bill. When you think about this question, you realize that the folks at Microsoft have really bestowed on us a number of tools over the years. Being a Lotus user for 15 years, I recognize Bill's method as the classic method for "quick-and-dirty" data manipulation from the good old days of Lotus release 2.1. He asks, is there an easier way to arrive at a unique list of account codes with totals for each account? Bill then described his current Excel methodology which is similar to method 1 below in order to come up with a unique list of account codes, with plans to use a matrix of CSE formulas to get the totals. Each account code can occur multiple times. At the end of the month, I need to eliminate the redundant data and come up with a total by account code. I build a monthly transaction list in Excel. Bill asked this week's question about redundant Excel data.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |