98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel - Data Sorting
Excel - Data Sorting
Resolved · Low Priority · Version 2003
Thu 6 May 2010, 14:43Delegate Julie said...
Julie has attended:
Excel Intermediate course
Excel VBA Intro Intermediate course
Excel Advanced course
Upgrade to Office 2007 course
Excel Advanced course
PowerPoint Introduction course
Word Intermediate course
Word Advanced course
PowerPoint Intermediate Advanced course
Excel - Data Sorting
Hi (me again),
I want to sort a list of numbers into pairs, i.e. £1,000 & -£1,000, £2,000 and -£2,000.
Basically, for each value I have a positive and negative which I want to keep together.
If I data sort by ascending I get all the negatives together followed by all the positives.
How can I sort my values into ascending order ignoring signage please?
Jules
Thu 6 May 2010, 16:07Trainer Stephen said...
RE: Excel - Data Sorting
Hi Julie
Thanks for your question.
This is fairly straightforward. Add an additional column, into which you place the ABS function. This returns the absolute value of a number, i.e. it removes the sign, so -200 becomes 200. You can then sort on this column.
Regards
Stephen
Thu 6 May 2010, 19:21Delegate Julie said...
RE: Excel - Data Sorting
Thanks Stephen - now why didn't I think of that.
One more quick question if I may, when I do MATCH function, if there is no match a #N/A is returned. How can I return a text message such as "no match", instead of #N/A?
I have tried the following:
if(iserror(match(a2,a2:a100,0),"no match",match(a2,a2:a100,0))
Thanks Stephen.
Jules
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Closing Multiple Open Worksheets At OnceWhen multiple Excel worksheets are opening, rather than performing a File > Close menu option multiple times, hold down the the SHIFT key and select the File > Close All menu option. |
