Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 10-08-2017, 05:23 AM
nrsmd nrsmd is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Nov 2014
Posts: 22
nrsmd is on a distinguished road
Default 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
Attached Files
File Type: xlsx practice1.xlsx (10.8 KB, 11 views)
Reply With Quote
  #2  
Old 10-08-2017, 10:40 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 436
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 10-08-2017, 11:54 AM
nrsmd nrsmd is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Nov 2014
Posts: 22
nrsmd is on a distinguished road
Default

I am more than willing to learn.

Thank you
Reply With Quote
Reply

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


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


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft