Microsoft Office Forums Sorting problem in Excel
 Register FAQ Search Today's Posts Mark Forums Read

#1
10-08-2017, 05:23 AM
 nrsmd Windows 7 64bit Office 2013 Novice Join Date: Nov 2014 Posts: 22
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.

My email is nathan_strahl@yahoo.com
Attached Files
 practice1.xlsx (10.8 KB, 11 views)
#2
10-08-2017, 10:40 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 436

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
10-08-2017, 11:54 AM
 nrsmd Windows 7 64bit Office 2013 Novice Join Date: Nov 2014 Posts: 22

I am more than willing to learn.

Thank you

 Tags excel 2010, sort

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post jmattingly85 Excel 2 08-17-2017 01:53 AM Rhene Excel 0 08-02-2017 11:20 AM mikehende Excel 12 09-12-2012 12:00 PM gsrikanth Excel 4 02-16-2012 12:30 AM Jonre Excel 2 08-21-2009 02:38 AM

All times are GMT -7. The time now is 11:16 AM.

 -- Default Style -- Mobile Style Contact Us - Privacy Statement - Top