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
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
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
