#1
|
|||
|
|||
Sorting problem in Excel
I have a sorting problem. I have attached a copy of the excel file (Excel 2010, Windows 7).
The name of the person needs to be sorted alphabetically. For example, information about Jones (A1) is located A2, A3, A4, A5, A6, and A7 through Z2, Z3, Z4, Z5, Z6, and Z7. For example, for Jones, in B2 he paid $1. in C5 he paid $6, in X3 he paid $12.00, and so on. The Grand Total paid in in Y1 Then, starting in A9, we start the exact same again with Mr. Anderson, with the next 6 rows dedicated to him (A9 through A15 continuing through Z through Z15. Now, I want to put Mr. Anderson and ALL his information above Joone's information, as a sort. THANKING YOU IN ADVANCE. My email is nathan_strahl@yahoo.com |
#2
|
|||
|
|||
You can't! And you can't use this worksheet as source for any formulas, unless you get every single value as link to certain cell.
Design your data entry table as structured one - data in columns where every column is destined for certain type of information, and all data from one batch in single row. P.e. you have a table with columns Person Date TransactionNo TransactionType Amount and one transaction will be a row in this table. It is up to you, allow you several transactions for same person on same day or not. NB! Person and date must be filled for every row! To ensure you make no typos entering persons, add a worksheet where you register all persons, and use it as source for data validation list in data entry table - you can select persons entered on Persons sheet, and you can't enter ones missing from there. To get sums you have several options. a) Design one or several report sheets, which read data from Persons sheet and from data entry sheet; b) On data entry sheet, at top of sheet add a couple empty rows, and in topmost one calculate the sum of filtered amounts using SUBTOTAL function. Set autofilter on data entry table, and you can calculate sum of amounts for any combination of Persons and dates setting autofilter for table; c) On Persons sheet, add a column for Amount, and a separate cell where you can enter a date. Use SUMIFS formula to calculate total amount for every person for date, entered into this cell. Or have 2 cells for dates, and calculate total amounts for period defined with those dates. |
#3
|
|||
|
|||
I am more than willing to learn.
Thank you |
Tags |
excel 2010, sort |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sorting Excel Spreadsheet | jmattingly85 | Excel | 2 | 08-17-2017 01:53 AM |
Sorting Charts - series custom color problem | Rhene | Excel | 0 | 08-02-2017 11:20 AM |
Problem sorting names alphabetically? | mikehende | Excel | 12 | 09-12-2012 12:00 PM |
sorting problem | gsrikanth | Excel | 4 | 02-16-2012 12:30 AM |
Sorting columns in Excel - please advise | Jonre | Excel | 2 | 08-21-2009 02:38 AM |